One for the advanced programmers!

I have a string column but I need to check if it contains any numeric
data ( in a WHERE Clause).
I did the following without any success
WHERE CONVERT(int,geocode) > 0
Gives an error as soon as it finds any non-numeric data.
Thanks> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
Try:
WHERE geocode LIKE '%[0-9]%'
Hope this helps.
Dan Guzman
SQL Server MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S Chapman wrote:
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
WHERE geocode NOT LIKE '%[^0-9]%'
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
CREATE TABLE Sometable (currencyvalues VARCHAR(20) PRIMARY KEY)
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('xnl')
INSERT INTO Sometable VALUES ('-23.15')
INSERT INTO Sometable VALUES ('muu')
SELECT * FROM Sometable WHERE currencyvalues LIKE '%[0-9]%'
--or
SELECT * FROM Sometable WHERE PATINDEX('%[0-9]%',currencyvalues)>0
Be aware that SQL Server will not probably use an index on the columnn ( if
you have one)
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S wrote on 20 Feb 2006 07:07:23 -0800:

> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
Are you look for numbers within the column, or a column that is just a
number? If the latter then you can using this:
WHERE ISNUMERIC(geocode) = 1
Dan|||Daniel
It will resturns wrong result as well as ISNUMERIC function is inaccurate
http://www.aspfaq.com/show.asp?id=2390
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('.')
SELECT * FROM Sometable WHERE ISNUMERIC(currencyvalues)=1
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%237y6iGjNGHA.740@.TK2MSFTNGP12.phx.gbl...
>S wrote on 20 Feb 2006 07:07:23 -0800:
>
> Are you look for numbers within the column, or a column that is just a
> number? If the latter then you can using this:
> WHERE ISNUMERIC(geocode) = 1
>
> Dan
>