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.
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%'
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]$)/
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]$)/
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
