DataAnalyst011 (6/27/2013)
...here's an example of another version I've seen: NOT LIKE '%[^0-9]%'
This is another regular expression. This basically is saying give me all the rows where the value is not like NOT a number. It is kind of a double negative. The first one you posted checks if there is a number anywhere in the string, this one makes sure that every single character is a number.
Here is a code example to show you what I mean.
declare @SearchVal varchar(10) = 'asdf1234asdf'
select 'yes'
where @SearchVal like '%[0-9]%'
select 'yes'
where @SearchVal NOT LIKE '%[^0-9]%'
set @SearchVal = 'asdf'
select 'yes'
where @SearchVal like '%[0-9]%'
select 'yes'
where @SearchVal NOT LIKE '%[^0-9]%'
set @SearchVal = '2345'
select 'yes'
where @SearchVal like '%[0-9]%'
select 'yes'
where @SearchVal NOT LIKE '%[^0-9]%'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/