SQL Server 2014:正则表达式到子字符串 [英] SQL Server 2014: Regex to substring

问题描述

在 SQL SERVER 2014 中如何使用正则表达式来提取字符串?例子:

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 将返回 John Lee

SQL will return John Lee

正则表达式是 ^CN=([^0-9]+) 但是如何应用这个正则表达式是 SQL ?

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

推荐答案

RegEx 和 SQL Server 不是最好的朋友...

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

你可以这样尝试:

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)

结果

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

更新更直接的方法...

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))));

这篇关于SQL Server 2014:正则表达式到子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！