Post reply

small query help me out

  • October 22, 2008 at 11:43 am

    #214286

    i have a column name --- ordernumber varchar(13) from table1

    all i want to know is how many of them in ths have some kind of strings and how many does have only numbers ......

    plz let me know how to query it

    like

    ordernumber ---3433

    and

    order number ---m343

    i should idff both how coud i do that

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • October 22, 2008 at 11:50 am

    #888256

    Try this. You can use the isnumeric function to test the value.

    Select Count(*) NumericCount

    From Table1

    Where isnumeric(ordernumber) = 1

    Select Count(*) NonNumericCount

    From Table1

    Where isnumeric(ordernumber) = 0

  • October 22, 2008 at 12:05 pm

    #888268

    THKZ SIMMONS IT WORKED ...

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • October 25, 2008 at 6:56 pm

    #889698

    Chris (10/22/2008)

    THKZ SIMMONS IT WORKED ...

    Really? Try these and see what happens... 😉

    SELECT ISNUMERIC('3D2'), ISNUMERIC('3E2'), ISNUMERIC('1,000'), ISNUMERIC(CHAR(13)), ISNUMERIC(CHAR(9))

    One way to beat this is...

    SELECT ISNUMERIC('3D2'+'.d0'), ISNUMERIC('3E2'+'.d0'), ISNUMERIC('1,000'+'.d0'), ISNUMERIC(CHAR(13)+'.d0'), ISNUMERIC(CHAR(9)+'.d0')

    --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)

  • October 27, 2008 at 1:38 am

    #889849

    Jeff, I think your example is somewhat misleading...

    In the first query SQL converts all the inputs into the most datatype of highest precedence possible, i.e Binary, which is always Numeric.

    In the second example the 'safest' datattype is the assumption of Text, and again an expected result

    If you define cast or convert your values implicitly to a datatype SQL does not assume anything

    In a table, the datatype will be defined (I am assuming ) as a character based and therefore SQL will test if the cahracter string represents numbers without the converting to binary first.

  • October 27, 2008 at 2:06 am

    #889852

    I want to Correct myself....

    I just tested this again and even explicit conversion gives the result as in Jeffs first example. I played some more and it appears that only the first charater is being evaluated.

    weird

  • October 27, 2008 at 2:17 am

    #889854

    IsNumeric returns true if the value passed to it can be successfully converted into any one of the numeric datatypes.

    3D2 and 3E2 can be cast to float (and mean 300), but not to numeric, decimal, money or int.

    1,000 can be cast to money (as it allows thousand separators), but not to float, numeric, decimal or int

    CHAR(13) and Char(9) can both be cast to money, but not to float, numeric, decimal or int

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 27, 2008 at 2:27 am

    #889860

    Interesting... Thanks Gail

  • October 27, 2008 at 5:21 am

    #889903

    AnzioBake (10/27/2008)

    I want to Correct myself....

    I just tested this again and even explicit conversion gives the result as in Jeffs first example. I played some more and it appears that only the first charater is being evaluated.

    weird

    Good. The real thing I'm trying to point out is that IsNumeric must never be used as an "IsAllDigits" function.

    --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)

  • October 27, 2008 at 1:35 pm

    #890215

    Jeff...thkz for the response could plz clarify this....

    so how should i consider it .....

    select isnumeric(order_number+'.do') from tbl1..

    is this the syntax...

    i dont understand this query

    SELECT ISNUMERIC('3D2'+'.d0'), ISNUMERIC('3E2'+'.d0'), ISNUMERIC('1,000'+'.d0'), ISNUMERIC(CHAR(13)+'.d0'), ISNUMERIC(CHAR(9)+'.d0')

    when i execute all the output is zero but here how abt 1000..could u plz explain a bit more ....

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • October 27, 2008 at 1:43 pm

    #890220

    hi evryone...

    now it makes me more confusing than before....

    select isnumeric('3edf2') when i tried this it showing as 0 ...wats the reason for this...why is isnumeric('3d2') showing as 1...plz do let meknow and even char(15) showing as 0 and char(13) as 1...

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • October 27, 2008 at 2:06 pm

    #890234

    Chris (10/27/2008)

    Jeff...thkz for the response could plz clarify this....

    so how should i consider it .....

    select isnumeric(order_number+'.do') from tbl1..

    is this the syntax...

    i dont understand this query

    SELECT ISNUMERIC('3D2'+'.d0'), ISNUMERIC('3E2'+'.d0'), ISNUMERIC('1,000'+'.d0'), ISNUMERIC(CHAR(13)+'.d0'), ISNUMERIC(CHAR(9)+'.d0')

    when i execute all the output is zero but here how abt 1000..could u plz explain a bit more ....

    You can do it with a different approach. Try to check if the string has at least one character that is not a digit. You can do it this way:

    select Col1

    from YourTable

    where YourCol not like '%[^0-9]%' and YourCol <> ''

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 27, 2008 at 2:17 pm

    #890243

    hey adi..

    when i ran this query with like even i got '000609876159' which should be in not like

    select Col1

    from YourTable

    where YourCol not like '%[^0-9]%' and YourCol <> ''

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

  • October 27, 2008 at 2:22 pm

    #890249

    Chris (10/27/2008)

    hey adi..

    when i ran this query with like even i got '000609876159' which should be in not like

    select Col1

    from YourTable

    where YourCol not like '%[^0-9]%' and YourCol <> ''

    In that case try adding the criteria and YourCol not like '0%'

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 27, 2008 at 2:28 pm

    #890251

    adi i tried that but u know what its not showing up 000609876159 in not like but its totally numeric.....

    Thanks,
    Chinna

    Its the Journey which gives you Happiness not the Destination-- Dan Millman

Viewing 15 posts - 1 through 15 (of 21 total)

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