SQL Server截取字符串函数操作常见方法

　　--截取分隔符之间的字符串

　　--ChristopherChen 20160804

　　USE TSQL2012;

　　IF OBJECT_ID('dbo.StringTest', 'U') IS NOT NULL

　　DROP Table dbo.StringTest;

　　GO

　　CREATE TABLE dbo.StringTest

　　(

　　UserId INT NOT NULL PRIMARY KEY,

　　NotificationUsers NVARCHAR(4000) NOT NULL

　　);

　　INSERT INTO dbo.StringTest VALUES

　　(7652,'G:q2p9e36a-79f1-4ac7-a869-db70917fa49e:Sherman@earthquake.com');

　　--法一, 通过游标截取相应的字符串

　　DROP TABLE #t1;

　　CREATE TABLE #t1

　　(

　　UserId INT NOT NULL,

　　NotificationUsers NVARCHAR(4000) NOT NULL

　　);

　　DECLARE @UserId INT, @NotificationUser VARCHAR(4000),@NewList VARCHAR(4000);

　　DECLARE N_Cursor CURSOR FOR

　　SELECT UserId, NotificationUsers

　　FROM dbo.StringTest

　　OPEN N_Cursor

　　FETCH NEXT FROM N_Cursor INTO @UserId, @NotificationUser

　　WHILE @@FETCH_STATUS =0

　　BEGIN

　　SET @Newlist = '';

　　WHILE CHARINDEX('|',@NotificationUser)>0

　　BEGIN

　　SET @NewList = @NewList + stuff(LEFT(@NotificationUser,CHARINDEX('|',@NotificationUser)-1),1, 39,'') + ','

　　SET @NotificationUser=STUFF(@NotificationUser,1,CHARINDEX('|',@NotificationUser),'')

　　END

　　SET @NewList = @NewList + stuff(@NotificationUser,1,39,'')

　　INSERT #t1 VALUES(@UserId, @NewList)

　　FETCH NEXT FROM N_Cursor INTO @UserId, @NotificationUser

　　END

　　CLOSE N_Cursor;

　　DEALLOCATE N_Cursor;

　　SELECT UserId,NotificationUsers FROM #t1;

　　DROP TABLE #t1;

　　--法二, 通过PARSENAME()函数截取指定位置的字符

　　--需要注意的是PARSENAME()原本是为截取四段式Server连接串或IP设计的，仅支持四段内的以'.'作分隔符的字符串;另外PARSENAME函数分段参数依据倒序排列

　　SELECT UserId

　　--截取中间部分

　　,REPLACE(PARSENAME(REPLACE(REPLACE(NotificationUsers,'.','&'),':','.'),2),'&','.') Id

　　--截取末端部分

　　,REPLACE(PARSENAME(REPLACE(REPLACE(NotificationUsers,'.','&'),':','.'),1),'&','.') Email

　　FROM dbo.StringTest

　　--法三, charindex与substring函数组合截取字符

　　--注意表达式charindex(':',NotificationUsers,charindex(':',NotificationUsers)+1)实际取得的是第二个':'的索引值

　　SELECT NotificationUsers

　　--截取中间部分

　　,substring(NotificationUsers,charindex(':',NotificationUsers)+1,case when charindex(':',NotificationUsers)=0 then len(NotificationUsers) else charindex(':',NotificationUsers,charindex(':',NotificationUsers)+1)-charindex(':',NotificationUsers)-1 end) Id

　　--截取末端部分

　　,right(NotificationUsers,len(NotificationUsers)-charindex(':',NotificationUsers,charindex(':',NotificationUsers)+1)) Email

　　FROM dbo.StringTest

　　--法四, 解析XML

　　WITH CTE AS (

　　SELECT UserId,NotificationUsers,convert(xml,''+replace(NotificationUsers,':','')+'') NotificationUsersXML from dbo.StringTest

　　)

　　SELECT *

　　--截取中间部分

　　,NotificationUsersXML.value('(/s)[2]','nvarchar(max)') Id

　　--截取末端部分

　　,NotificationUsersXML.value('(/s)[3]','nvarchar(max)') Email

　　FROM CTE