how to perform regEx in SQL

[camy123]

hi i know you cant do regex in SQL
but i ahve the following problem
bascially i want to bring out all the values which have the following values in includes in them
for example

123456
666555
777888

so that should bring out
abc123456
cde666555
efgh777888


can any body help me.

 

[JamesLean]

For simple things like this you can use LIKE:

SELECT col
FROM table
WHERE col LIKE '%123%'

Look it up in BOL for all the options.

--James

 

[camy123]

thnak you but the fact is i have more then one value then 123

for example
i have

col1
123456
786543
555555

how can i incorporate the like statement to check all

 

[JamesLean]

Do you mean:

SELECT col
FROM table
WHERE col LIKE '%123%'
  OR col LIKE '%456%'
  OR col LIKE '%789%'

--James

 

[camy123]

thanks alot mate if i was to do it by looking inanother table(temptable) for example if i stored
12345
34567

the following wont work

select col1 from table1 where like
(select col1 from table2)

ne suggestions.. please
and thanks again

 

[JamesLean]

You could do:

SELECT DISTINCT t1.col1
FROM main_table t1 JOIN lookup_table t2 ON t1.col1 LIKE '%' + t2.col1 + '%'

--James