CODEWITHSUNDEEP
sqlsql-serverrange
Louis
Louis

Reputation: 660

Wildcard of Number Ranges in SQL Server

The pattern match in sql server doesn't work like regex. Is it possible to use LIKE to match number ranges? I need to do something like this:

ABCD 1
ABCD 2
ABCD 3
ABCD 4
ABCD 8
ABCD 9
ABCD 10
...
ABCD 20

I want to select all data from "ABCD 1" to "ABCD 20". But I want to ignore anything between "ABCD 4" and "ABCD 8". Of course I can do OR conditions, but I just want to check if there is more elegant way.

TIA.

Upvotes: 3

Views: 10298

Answers (3)

Alberto Solano
Alberto Solano

Reputation: 8227

You can use the LIKE operator and specify your pattern executing a query like this one:

SELECT mydata FROM mytable
WHERE(mydata LIKE 'ABCD [1-9]' OR mydata LIKE 'ABCD 1[0-9]' OR mydata LIKE 'ABCD 20') 
AND mydata NOT LIKE 'ABCD [4-8]';

or, something more concise and shorter:

SELECT mydata FROM mytable
where mydata like 'ABCD [^4-8]%';

Have a look at this SQL Fiddle.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You could use like in SQL Server like this:

where col like 'ABCD [1-9]' or
      col like 'ABCD 1[0-9]' or
      col like 'ABCD 20'

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select * from tablename
where cast(replace(somecolumn, 'ABCD','') as numeric) < 4
and cast(replace(somecolumn, 'ABCD','') as numeric) > 8

Upvotes: 0

Related Questions