SQL Server 2014: Regex to substring

问题:

In SQL SERVER 2014 how to use regex to extract string ? example:

CN=John Lee  709123,ou=Users,OU=LA-US1242,OU=US,OU=nam,DC=DIR,DC=ABB,DC=com

SQL will return John Lee

The regex is ^CN=([^0-9]+) but how to applied this regex is SQL ?

回答1:

RegEx and SQL Server are not the best friends...

You might try it like this:

DECLARE @s NVARCHAR(100)=N'CN=John Lee  709123,ou=Users,OU=LA-US1242,OU=US,OU=nam,DC=DIR,DC=ABB,DC=com';

WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@s,',','</x><x>') + '</x>' AS XML) AS AsXml
) 
SELECT LEFT(part,Pos.Eq-1) AS KeyName
      ,SUBSTRING(part,Pos.Eq +1,1000) AS KeyValue
FROM Splitted
CROSS APPLY AsXml.nodes('/x') AS A(a) 
CROSS APPLY (SELECT a.value('.','nvarchar(max)')) AS The(part)
CROSS APPLY(SELECT CHARINDEX('=',part)) AS Pos(Eq)

The result

Key Value
-----------------
CN  John Lee  709123
ou  Users
OU  LA-US1242
OU  US
OU  nam
DC  DIR
DC  ABB
DC  com

UPDATE A more straight approach...

DECLARE @s NVARCHAR(100)=N'CN=John Lee  709123,ou=Users,OU=LA-US1242,OU=US,OU=nam,DC=DIR,DC=ABB,DC=com';

DECLARE @partRev NVARCHAR(100)=REVERSE(SUBSTRING(@s,CHARINDEX('=',@s)+1,CHARINDEX(',',@s)-4));
SELECT LTRIM(RTRIM(REVERSE(SUBSTRING(@partRev,CHARINDEX(' ',@partRev),1000))));

回答2:

Another very efficient way to solve this:

-- your sample data
DECLARE @s NVARCHAR(100)=N'CN=John Lee  709123,ou=Users,OU=LA-US1242,OU=US,OU=nam,DC=DIR,DC=ABB,DC=com';

-- solution
SELECT SUBSTRING(s, s1, s2-s1) 
FROM (VALUES (@s)) t(s)
CROSS APPLY (VALUES (charindex('CN=', @s)+3, patindex('%[0-9]%', @s))) tt(s1, s2);