Regular expresions MS SQL. How use in select.

Question

I need to write select that gives me records which in field SerialNumber have only string like this MT and some numbers. For example MT34234234 or MT455. I dont want other records for example with value MTA424 or MT23423423BBA. I write this reqular expresion:

select * from 
MyTable
where  SerialNumber like 'MT%[0-9]'

But it gives me record with value MTA424. Can somebody help me how i can fix my query?

Answer 1

You can use a combination of checking for MT at the beginning of the string and then non-numeric characters after the first 2 places of the string.

declare @table table (SerialNumber varchar(64))
insert into @table
values
('MT34234234'),
('MT455'),
('MTA424'),
('MT23423423BBA')

select * from @table
where SerialNumber like 'MT%'
and right(SerialNumber,len(SerialNumber) - 2) not like '%[^0-9]%'

Answer 2

Like @Ryan said, there are no real regular expressions in SQL-Server. However, you can get to your desired selection by adding a second condition like

select * from MyTable
where  SerialNumber like 'MT%'
and SerialNumber not like 'MT%[^0-9]%'

(I modified the first condition too.)

Answer 3

SELECT * FROM MyTable
WHERE SerialNumber LIKE 'MT%'
    AND SerialNumber NOT LIKE 'MT%[a-zA-Z]%'

Answer 4

If you are using Postgresql you can do something like this:

select * from 
MyTable
where  regexp_like(SerialNumber, '^MT\d*') // or '^MT\d+' as you want

Answer 5

Almost there! Just try this:

select  *
from    mytable
where   serialnumber like 'MT[0-9]%[^a-z]'