CODEWITHSUNDEEP
sql-server
Attitude Black
Attitude Black

Reputation: 31

Writing a REGEX function in SQL Server

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:

Table

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

Upvotes: 0

Views: 379

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

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

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

Upvotes: 1

Related Questions