How can I search for numbers in a varchar column

Question

I've got a simple nvarchar(25) column in an SQL database table. Most of the time, this field should contain alphanumeric text. However, due to operator error, there are many instances where it contains only a number. Can I do a simple search in SQL to identify these cases? That is, determine which rows in the table contain only digits in this column. As an extension, could I also search for those column values which contain only digits and a space and/or slash.

In other languages (eg. Perl, Java) a regular expression would resolve this quickly and easily. But I haven't been able to find the equivalent in SQL.

Answer 1

yes you can achive this by using isnumeric function available in sql sever

check more at this link : http://msdn.microsoft.com/en-us/library/aa933213(SQL.80).aspx

Answer 2

select column_name from table_name where IsNumeric(column_name) <> 1

Answer 3

Numeric Only:

 SELECT * FROM Table WHERE ISNUMERIC(Field) = 1

With Space:

 SELECT * FROM Table WHERE Field LIKE '% %'

With Slash:

 SELECT * FROM Table WHERE Field LIKE '%/%'

Combined:

 SELECT * FROM Table WHERE ISNUMERIC(Field) = 1 OR  Field LIKE '% %' OR Field LIKE '%/%'