Testing if a nvarchar is number in SQL

Posted on 2003-03-19
Medium Priority
Last Modified: 2008-03-06
I have a table that contain nvarchar

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.
Question by:Markandrei
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 18

Expert Comment

ID: 8165770
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.
LVL 18

Expert Comment

ID: 8165775
where col1 like '%[^0-9]%)
or len(col1) - len(replace(col1,'.'.'')) > 1

will get those with two decimal points as well.

Accepted Solution

DaniPro earned 400 total points
ID: 8165787
If you use SQL Server you can use this sample SQL:
select * from YourTable Where isnumeric(YourField) = 0;
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 8165801
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
333 33
55 55 55 55 55
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.

Author Comment

ID: 8165811
Thanks man you are the best!
LVL 18

Expert Comment

ID: 8166029
isnumeric will work for the examples you give but it does not guarantee that convert(int,...) will not give an error.


Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question