Post reply

how to identify numbers in varchar column

  • 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

  • May 25, 2010 at 6:51 pm

    #1172422

    -- 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://www.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

  • 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

  • May 25, 2010 at 7:57 pm

    #1172431

    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

  • May 25, 2010 at 8:08 pm

    #1172434

    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]%'

  • May 25, 2010 at 8:20 pm

    #1172437

    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://www.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)

  • May 25, 2010 at 8:22 pm

    #1172438

    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)

  • May 25, 2010 at 8:30 pm

    #1172442

    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 🙂

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

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