• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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.
0
Markandrei
Asked:
Markandrei
  • 3
  • 2
1 Solution
 
nigelrivettCommented:
Depends what you mean by not a number

where col1 like '%[^0-9]%)
will get all with a char which is not in 0-9.
0
 
nigelrivettCommented:
where col1 like '%[^0-9]%)
or len(col1) - len(replace(col1,'.'.'')) > 1

will get those with two decimal points as well.
0
 
DaniProCommented:
If you use SQL Server you can use this sample SQL:
select * from YourTable Where isnumeric(YourField) = 0;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MarkandreiAuthor Commented:
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.
0
 
MarkandreiAuthor Commented:
Thanks man you are the best!
0
 
nigelrivettCommented:
isnumeric will work for the examples you give but it does not guarantee that convert(int,...) will not give an error.


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now