Markandrei
asked on
Testing if a nvarchar is number in SQL
I have a table that contain nvarchar
Col1
222
22 2
2 22
The problem is I want to select everything that's not a number (ex 22 2 and 2 22). Does anyone know how can I do that. Im interesting in a function like isnan in Javascript.
Col1
222
22 2
2 22
The problem is I want to select everything that's not a number (ex 22 2 and 2 22). Does anyone know how can I do that. Im interesting in a function like isnan in Javascript.
where col1 like '%[^0-9]%)
or len(col1) - len(replace(col1,'.'.'')) > 1
will get those with two decimal points as well.
or len(col1) - len(replace(col1,'.'.'')) > 1
will get those with two decimal points as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok let me be more explicit. I need to know if the convert(int,col1) will work. The problem is that the information in database is not after a patern for example
2 3333 3
3333333
333 33
55 55 55 55 55
33
I want to make a select that return 3333333 and 33 wich will work with convert and another select with the other values that will not work with convert because of the white spaces.
2 3333 3
3333333
333 33
55 55 55 55 55
33
I want to make a select that return 3333333 and 33 wich will work with convert and another select with the other values that will not work with convert because of the white spaces.
ASKER
Thanks man you are the best!
isnumeric will work for the examples you give but it does not guarantee that convert(int,...) will not give an error.
where col1 like '%[^0-9]%)
will get all with a char which is not in 0-9.