T-SQL Equivalent of regular expression ‘b’

I’m in the process of converting a CLR function that utilizes regex to a SQL function. I know that SQL Server does not fully support regex, but i just need one case to work which is a word search.

Search Field Value: {"Id":1234, "Title": "The quick brown"}

Regex Pattern in .NET: 'b' + '"Id":1234' 'b'

I want to be able to match when Search field contains “Id”:1234 but not “Id”:12345

How do I achieve this? I’ve tried this but obviously doesn’t work when the number 1234 ends with space or b instead of comma.

DECLARE @match bit = 0
DECLARE @input nvarchar(max) = '{"Id":1234, "Title": "The quick brown"}'
DECLARE @pattern nvarchar(max) ='"Id":1234'

SELECT @match = 1 FROM (VALUES ('')) table1 (column1)
        WHERE @Input LIKE '%' + @pattern + ',%'

Answer

I think pattern '%"Id":1234[^a-zA-Z0-9]%' will do.

It uses negated character class [^a-zA-Z0-9], which works as in regular regex 🙂

Sample:

declare @tbl table (col varchar(100));
insert into @tbl values 
('{"Id":1234, "Title": "The quick brown"}'),
('{"Id":1234, "Title": "The quick brown"}'), 
('Id":1234, "Title": "The quick brown"}'), 
('{"Id":12345, "Title": "The quick brown"}');

select *
from @tbl
where col like '%"Id":1234[^a-zA-Z0-9]%'