Get T-SQL value using Regular Expressions

Question

I have the following string in database:

SET @Value = "[4888378977CA4A5] Test String"

Also, the @Value may also have 'RE: ' at the start of the string such as:

SET @Value = "RE: [4888378977CA4A5] Test String"

How can I access the 4888378977CA4A5 part of the string using regular expressions?

Thanks!

Answer 1

T-SQL doesn't have native regex support. You can use a CLR function to access .NET regex functionality or use PatIndex if the pattern is simple.

Or if you just want to get the contents of [...] maybe CharIndex would work.

;with strings as
(
SELECT 'no match' AS string UNION ALL
SELECT '[4888378977CA4A5] Test String' UNION ALL
SELECT 'RE: [Other Value] Test String' 
)
select substring(string,
             charindex('[',string)+1,
             charindex(']',string, charindex('[',string))-charindex('[',string)-1)
                                                                          AS result
from strings
where string like '%/[%/]%' ESCAPE '/'

Returns

result
-----------------------------
4888378977CA4A5
Other Value

Answer 2

This is a way to do it in SQL Server 2000 using VBScript.RegExp.

However, in SQL Server 2005 and SQL Server 2008 you would be better off using a the .NET Regex Library, as illustrated in here.

Answer 3

The regex would be \[([A-Z0-9]+)\]. Extracting the substring in the captured group depends on your regex flavor. It could be $1, \1, match.groups(1) etc.