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 ?
-
use "Like" clause stackoverflow.com/questions/29206404/mssql-regular-expressionDanil.V– Danil.V2016-12-12 15:47:01 +00:00Commented Dec 12, 2016 at 15:47
Add a comment
|
2 Answers
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))));
answered Dec 12, 2016 at 15:53
Gottfried Lesigang
67.6k9 gold badges60 silver badges124 bronze badges
1 Comment
user1902849
This an alternative way to solve this problem. Thanks
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);
