+ Reply to Thread
Results 1 to 4 of 4

how to check if value is not numeric?

  1. how to check if value is not numeric?

    Select * from tbl1
    where Right(Alpha_Numeric, 5) ... Is not numeric

    a value for Alpha_Numeric might be 'abc12345' or 'abc123mm'

    I need to retrieve the row where Alpha_Numeric Like 'abc123mm'

    How does Tsql determine if a char string is numeric or not?

    Thanks,
    Rich

  2. RE: how to check if value is not numeric?

    Well, I just discovered the IsNumeric function. Now I know how to do this.

    "Rich" wrote:

    > Select * from tbl1
    > where Right(Alpha_Numeric, 5) ... Is not numeric
    >
    > a value for Alpha_Numeric might be 'abc12345' or 'abc123mm'
    >
    > I need to retrieve the row where Alpha_Numeric Like 'abc123mm'
    >
    > How does Tsql determine if a char string is numeric or not?
    >
    > Thanks,
    > Rich


  3. Re: how to check if value is not numeric?

    Rich (Rich@discussions.microsoft.com) writes:
    > Select * from tbl1
    > where Right(Alpha_Numeric, 5) ... Is not numeric
    >
    > a value for Alpha_Numeric might be 'abc12345' or 'abc123mm'
    >
    > I need to retrieve the row where Alpha_Numeric Like 'abc123mm'
    >
    > How does Tsql determine if a char string is numeric or not?


    It depends on exactly what you mean with numeric. There is a function
    isnumeric(), but it's useless, because it returns 1, if the value
    can be converted to any numeric data type, but you don't know which.

    In practice, most of the time what you are really looking for is "is
    string digits only"? In that case, this will do:

    SELECT * FROM tbl WHERE col LIKE '%[^0-9]%"




    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  4. Re: how to check if value is not numeric?

    Thanks. Yes. Your method is better.

    "Erland Sommarskog" wrote:

    > Rich (Rich@discussions.microsoft.com) writes:
    > > Select * from tbl1
    > > where Right(Alpha_Numeric, 5) ... Is not numeric
    > >
    > > a value for Alpha_Numeric might be 'abc12345' or 'abc123mm'
    > >
    > > I need to retrieve the row where Alpha_Numeric Like 'abc123mm'
    > >
    > > How does Tsql determine if a char string is numeric or not?

    >
    > It depends on exactly what you mean with numeric. There is a function
    > isnumeric(), but it's useless, because it returns 1, if the value
    > can be converted to any numeric data type, but you don't know which.
    >
    > In practice, most of the time what you are really looking for is "is
    > string digits only"? In that case, this will do:
    >
    > SELECT * FROM tbl WHERE col LIKE '%[^0-9]%"
    >
    >
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    >
    > Books Online for SQL Server 2005 at
    > http://www.microsoft.com/technet/pro...ads/books.mspx
    > Books Online for SQL Server 2000 at
    > http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >


+ Reply to Thread