Writing a REGEX function in SQL Server

Question

Right, so I am trying to write a REGEX function. Trying to write it as sort of a stored procedure, I guess (assuming that if that is the right way to go about it)?

Problem : I have a table with millions of rows. I would like to create a REGEX function that scrapes out the 6 alpha numeric CML code (mentioned in the table). Sometimes, it does not even mention the word CML and the code is still there. See Row 5 for reference below

My approach

SELECT * 
FROM [Reporting].[dbo].[Master] 
WHERE [Notes] LIKE '[C][M][L]%'

I am not too sure if that is the right way to go about it or not.

Desired result : I want to be able to write a code using a REGEX function which can look for such patterns in the notes column and put it as a separate column against its respective Part ID

This is the table:

Edit - A typo at my end occurred for L987234. The length of these characters is also 6, hence, it actually is L98723

If you want Regex functions in SQL Server, you need to use CLR objects. — Thom A
– Thom A ♦, Commented Aug 1, 2022 at 18:26
If you can't precisely describe what makes a valid CML code (other than being six characters) then how is a regex going to help? — shawnt00
– shawnt00, Commented Aug 1, 2022 at 18:32
Those 6 characters is the only way to identify it. Sometimes it might mention "CML" sometimes, it may not but based on my tribal knowledge, I know those 6 characters will always be the code I want to extract. — Attitude Black
– Attitude Black, Commented Aug 1, 2022 at 18:35
There is no in built support for Regex on SQL Server, @AttitudeBlack . To be able to use Regex you have to create CLR functions so that you can use those instead. — Thom A
– Thom A ♦, Commented Aug 1, 2022 at 18:37

Aaron Bertrand · Accepted Answer · 2022-08-01 21:21:31Z

1

Assuming SQL Server 2016 or greater:

SELECT m.<columns>,
  Code = s.value
FROM Reporting.dbo.[Master] AS m
CROSS APPLY STRING_SPLIT(m.Notes, ' ') AS s
 WHERE LEN(s.value) = 6
 AND s.value LIKE '%' + REPLICATE('[0-9A-Z]', 6) + '%'
 AND s.value LIKE N'%[0-9]%'  -- make sure it has at least one digit
 AND s.value LIKE N'%[A-Z]%'; -- make sure it has at least one letter

Example db<>fiddle

If you're on a lower version, then create your own function (like this one), then replace STRING_SPLIT with dbo.SplitString.

answered Aug 1, 2022 at 21:21

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Attitude Black Over a year ago

Thank-you. Let me give it a try and I shall let you know.

Attitude Black Over a year ago

Works. Thank-you very much. Appreciate it.

Collectives™ on Stack Overflow

Writing a REGEX function in SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related