Home Regular Expression MS-SQL Starts with RI and followed by two more defined characters and a space

Questions

Regular Expression MS-SQL Starts with RI and followed by two more defined characters and a space

byMR

May 1, 2024

I have a column that is a string and I want to find all records where the string starts with one of the following:

'RIRV', 'RIUI', 'RIRI', 'RICJ', 'RIRE', 'RIRF', 'RIAW', 'RIAS', 'RINA'

So they all start RI and must be followed by: 'RV|UI|CJ|RE|RF|AW|AS|NA) %'

I was using:

WHERE SUBSTRING(A.[pa-smart-comment], 1, 5) IN (
            'RIRV ', 'RIUI ', 'RIRI ', 'RICJ ', 'RIRE ','RIRF ','RIAW ','RIAS ','RINA '
            )

But I find the LIKE to be faster in use.

>Solution :

Combine a sarg-able LIKE with whatever you want to narrow the results. This will allow SQL to efficiently eliminate the rows not starting with ‘RI’ or not having a space after the first 4 characters.

WHERE A.[pa-smart-comment] like 'RI__ %'
  and SUBSTRING(A.[pa-smart-comment], 1, 4) IN (
            'RIRV', 'RIUI', 'RIRI', 'RICJ', 'RIRE','RIRF','RIAW','RIAS','RINA'
            )