question

prathik avatar image
prathik asked

Issue while using like operator with regex expression

Hi All,

I am facing issue while using like operator with regex expression.

Below is the sample SQL script

create table #Temp (displayname varchar(100) )

insert into #Temp values('SG_UNFE_01_VIPCP30DY')

select * from #Temp where displayname like '%^[A-Z][A-Z]_[A-Z][A-Z]FE_[0-9][0-9]_VIP.*%'

drop table #Temp

Please help, Thanks in advance.

sql-server-2012likefilterexpressionregex
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

You can't use REGEX syntax like that in T-SQL

What is it exactly the pattern you are trying to match?

The ^ denotes something to not match, so your first term after the % is saying match no characters A-Z. This on it's own will make the query return nothing as your example data does not fit.

The underscore _ matches a single character - if you want to match on it explicitly, then you have to escape it between square brackets []

The asterisk means nothing, so is trying to match exactly an asterisk

This query returns something

select * from #Temp 
where displayname like '%[A-Z][A-Z][_][A-Z][A-Z]FE[_][0-9][0-9][_]VIP%'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

prathik avatar image
prathik answered

Thank you so much @Kev Riley. this what i was expecting. can you help me for OR case in below RegEx.

/^[A-Z][A-Z]_UNFE_[0-9][0-9]_(!VIP|CC|CP|LN).*XDAY$/

/^TH_UNFE_[0-9][0-9]_LN(!_PDUE|XDAY|XDPD|IC|HI|MED)(.*|!30DY)$/

/^[A-Z][A-Z]_UNFE_[0-9][0-9]_(!VIP|CC|CP|LN).*(TDR$|TDR_[A-Z]$)/

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·

As @Kev Riley indicates, functionality within T-SQL for this sort of thing is quite limited. The full lack-of-power of the LIKE operator is detailed here: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017


However, somebody has made available a SQLCLR-based RegEx parser that might be of use to you: https://github.com/zzzprojects/Eval-SQL.NET/wiki/SQL-Server-Regex-%7C-Use-regular-expression-to-search,-replace-and-split-text-in-SQL

0 Likes 0 ·
prathik avatar image
prathik answered

can any one help me for OR case in below RegEx.

/^[A-Z][A-Z]_UNFE_[0-9][0-9]_(!VIP|CC|CP|LN).*XDAY$/

/^TH_UNFE_[0-9][0-9]_LN(!_PDUE|XDAY|XDPD|IC|HI|MED)(.*|!30DY)$/

/^[A-Z][A-Z]_UNFE_[0-9][0-9]_(!VIP|CC|CP|LN).*(TDR$|TDR_[A-Z]$)/

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.