how to identify numbers in varchar column

Question

Post reply

how to identify numbers in varchar column

anitha.cherukuri

SSCommitted

Points: 1514
More actions
May 25, 2010 at 5:53 pm

#221911

Hi

I am facing different issue here,

I got a column like this.

Formatted_Name (data type varchar)

john

peter

axis

phil

123

renu

aaa

9

..... etc

I want to write query that has to give only 123,9 etc....information.

can anybody tell me how to identify numbers in name column?

Let me know if its not clear or any other information

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

-- See how you start off by actually creating a table



-- and then inserting the data into it? Doing this really

-- makes things a lot easier for all the people you want to help

-- you. So, HELP US HELP YOU by doing this for us!

-- See http://qa.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @test-2 TABLE (col1 varchar(50))

INSERT INTO @test-2

SELECT 'john' UNION ALL

SELECT 'peter' UNION ALL

SELECT 'axis' UNION ALL

SELECT 'phil' UNION ALL

SELECT '123' UNION ALL

SELECT 'renu' UNION ALL

SELECT 'aaa' UNION ALL

SELECT '9'

-- whew!!! now that the test data has been made,

-- let's see how to do what you're asking for!

SELECT *

  FROM @test-2

WHERE IsNumeric(col1 ) = 1

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 2

anitha.cherukuri

SSCommitted

Points: 1514

May 25, 2010 at 7:14 pm

#1172425

Thanks wayne,

I did not realize that you need create and insert statements.

i am very sorry.

I will add next time!!!!!!

Anitha

WayneS SSC Guru Points: 95405 More actions · Answer 3

Not a problem... but it does GREATLY help us out... if we can just cut-and-paste some code that creates the problem you're trying to figure out, you will get a LOT more people willing to help you out. Many people that would otherwise provide some great answers will skip right by your question if you didn't bother to do this.

So, does this work for you?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

WayneS, as a classic question, do u really think ISNUMERIC can be used as an is_all_digits functions? There is nothing that a super fast coder like you don't know and am such a naive to comment on your code, but still, the following code will be fool proof right?

Using the fine example of yours,



DECLARE @test-2 TABLE (col1 varchar(50))

INSERT INTO @test-2

SELECT 'john' UNION ALL

SELECT 'peter' UNION ALL

SELECT 'axis' UNION ALL

SELECT 'phil' UNION ALL

SELECT '123' UNION ALL

SELECT 'renu' UNION ALL

SELECT 'aaa' UNION ALL

SELECT '9'

-- Sturdy check for is All Digits question.

SELECT * FROM @test-2 WHERE col1 NOT LIKE '%[0-9]%'

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

There is a bit of a problem here, folks. ISNUMERIC should NEVER (one of the few times I say "never" :-P) be used to detect if a varchar is all numeric digits because that's not what it was designed for. It was designed to figure out if a VARCHAR could be converted to some (any) numeric data type. For example and using Wayne's good test code with some additional values...

-- See how you start off by actually creating a table



-- and then inserting the data into it? Doing this really

-- makes things a lot easier for all the people you want to help

-- you. So, HELP US HELP YOU by doing this for us!

-- See http://qa.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @test-2 TABLE (col1 varchar(50))

INSERT INTO @test-2

SELECT 'john' UNION ALL

SELECT 'peter' UNION ALL

SELECT 'axis' UNION ALL

SELECT 'phil' UNION ALL

SELECT '123' UNION ALL

SELECT 'renu' UNION ALL

SELECT 'aaa' UNION ALL

SELECT '9' UNION ALL

SELECT '2d3' UNION ALL --Gets by

SELECT '2e3' UNION ALL --Gets by

SELECT '0,1,2,3,4' UNION ALL --Gets by

SELECT ' 12 ' UNION ALL --Gets by

SELECT '$' UNION ALL --Gets by

SELECT '-' UNION ALL --Gets by

SELECT '+' UNION ALL --Gets by

SELECT '.' UNION ALL --Gets by

SELECT CHAR(9) UNION ALL --Gets by

SELECT CHAR(11) UNION ALL --Gets by

SELECT CHAR(13) UNION ALL --Gets by

SELECT '0' --end of demo

-- whew!!! now that the test data has been made,

-- let's see how to do what you're asking for!

--==== This lets stuff isn't "all digits" go by.

SELECT *

  FROM @test-2

 WHERE IsNumeric(col1 ) = 1 

--===== This lets only numeric digits go by

 SELECT *

   FROM @test-2

  WHERE Col1 NOT LIKE '%[^0-9]%'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

ColdCoffee (5/25/2010)

WayneS, as a classic question, do u really think ISNUMERIC can be used as an is_all_digits functions? There is nothing that a super fast coder like you don't know and am such a naive to comment on your code, but still, the following code will be fool proof right?

Using the fine example of yours,

DECLARE @test-2 TABLE (col1 varchar(50)) INSERT INTO @test-2 SELECT 'john' UNION ALL SELECT 'peter' UNION ALL SELECT 'axis' UNION ALL SELECT 'phil' UNION ALL SELECT '123' UNION ALL SELECT 'renu' UNION ALL SELECT 'aaa' UNION ALL SELECT '9' -- Sturdy check for is All Digits question. SELECT * FROM @test-2 WHERE col1 NOT LIKE '%[0-9]%'

Heh... you beat me to it. I took a short break, came back, posted my code, and dang if ya didn't beat me to it. Your heart is in the right place but I believe you left out a "^" if you want to return all digits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 7

Jeff Moden (5/25/2010)

ColdCoffee (5/25/2010)

WayneS, as a classic question, do u really think ISNUMERIC can be used as an is_all_digits functions? There is nothing that a super fast coder like you don't know and am such a naive to comment on your code, but still, the following code will be fool proof right?

Using the fine example of yours,

DECLARE @test-2 TABLE (col1 varchar(50)) INSERT INTO @test-2 SELECT 'john' UNION ALL SELECT 'peter' UNION ALL SELECT 'axis' UNION ALL SELECT 'phil' UNION ALL SELECT '123' UNION ALL SELECT 'renu' UNION ALL SELECT 'aaa' UNION ALL SELECT '9' -- Sturdy check for is All Digits question. SELECT * FROM @test-2 WHERE col1 NOT LIKE '%[0-9]%'

Heh... you beat me to it. I took a short break, came back, posted my code, and dang if ya didn't beat me to it. Your heart is in the right place but I believe you left out a "^" if you want to return all digits.

😉 I could never beat you Jeff , not even in my dreams :-)! As a matter of fact, i just learnt that ISNUMERIC can be treacherous for is_all_digits from one of your posts earlier! So i just posted what i learnt, and now i am getting good name from the person who taught me that, feels good 🙂