SQL Statement using LIKE

Question

I want to know in a column named NUMTSO if there exists data with this format "WO#############", so what I'm doing is this:

select *
from fx1rah00
where numtso like 'WO[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

but I get nothing. What am I doing wrong?

Answer 1

What dbms is this? Some databases don't let use use regex in like clause just wildcards. If its oracle you could checkout REGEXP_LIKE or REGEXP for mysql.

I would do something like:

where NUMTSO like 'WO%'
and REGEXP_LIKE(NUMTSO, 'WO[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

by using the like and the regex check you can still range scan on an index if there was one.

Answer 2

This works fine for me in SQL Server. If you are not using SQL Server you will likely need some different syntax though as the pattern syntax is not standard SQL.

;with fx1rah00 As
(
select 'WO1234567890123' as numtso
)
select *
from fx1rah00
where numtso like 
             'WO[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Answer 3

The SQL standard does not support REGEXP in LIKE. They have a much more primitive pattern language. You'll need to add a function, or post-filter, or discover a DBMS-specific extension.

Answer 4

MySQL allows you to use regular expressions with the REGEXP keyword instead of LIKE. I suggest the following code:

SELECT *
  FROM `fx1rah00`
 WHERE `numtso` REGEXP 'WO[0-9]{13}'