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

admin MsSql

2023-12-01 0 460

1、SUBSTRING()函数

含义：从指定位置开始截取指定数量的字符。

使用：SUBSTRING(原字符,截取字符的起始位置,截取字符数量)

举例：

1、截取字符的起始位置是从1开始的，为0的话截取的字符数量会减1。

2、截取字符数量可以为0，但是为0的话得到的结果就是空，不可以为负数。

2、LEFT()函数

含义：从字符左边第1位开始截取指定数量的字符。

使用：LEFT(原字符,截取字符数量)

举例：

3、RIGHT()函数

含义：从字符右边第最后1位开始往前截取指定数量的字符。

使用：RIGHT(原字符,截取字符数量)

举例：

4、CHARINDEX()函数

含义：返回指定字符在原始值中的位置。

使用：CHARINDEX(指定字符,原字符)

举例：

1、指定字符可以多个字符，那样只会返回指定字符最左字符的位置。

5、LEN函数

含义：返回指定字段的长度。

使用：LEN(字段)

举例：

1、不知道要截取多少数量的时候可以使用。

补充知识：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,\'<s>\’+replace(NotificationUsers,\’:\’,\'</s><s>\’)+\'</s>\’) NotificationUsersXML from dbo.StringTest
)
SELECT *
–截取中间部分
,NotificationUsersXML.value(\'(/s)[2]\’,\’nvarchar(max)\’) Id
–截取末端部分
,NotificationUsersXML.value(\'(/s)[3]\’,\’nvarchar(max)\’) Email
FROM CTE