SQL separator string, SQL Separator

SQL separator string, SQL Separator

Create a function:

Create function [dbo]. [split] (@ str NVARCHAR (max), @ spliter NVARCHAR (10) -- @ str: Target string -- @ spliter: delimiter RETURNS @ tb TABLE (ch NVARCHAR (max )) ASBEGINDECLARE @ Num INT, @ Pos INT, @ NextPos INTSET @ Num = 0 SET @ Pos = 1 WHILE (@ Pos <= LEN (@ str )) BEGINSELECT @ NextPos = CHARINDEX (@ spliter, @ str, @ Pos) IF (@ NextPos = 0 OR @ NextPos is null) SELECT @ NextPos = LEN (@ str) + 1 insert into @ tb VALUES (RTRIM (LTRIM (SUBSTRING (@ str, @ Pos, @ NextPos-@ Pos) SELECT @ Pos = @ NextPos + 1 ENDRETURNENDGO

Usage:

SELECT * FROM dbo. split ('1, 2, 3, 4 ',', ') -- Note: Data Tables with multiple rows and one column

Test statement:

IF object_id (n' [dbo]. [split] ') is not null -- check function [[dbo]. [split]: whether the begin drop function [dbo] exists. [split] -- delete the endgocreate function [dbo]. [split] (@ str NVARCHAR (max), @ spliter NVARCHAR (10) -- @ str: Target string -- @ spliter: delimiter RETURNS @ tb TABLE (ch NVARCHAR (max )) ASBEGINDECLARE @ Num INT, @ Pos INT, @ NextPos INTSET @ Num = 0 SET @ Pos = 1 WHILE (@ Pos <= LEN (@ str )) BEGINSELECT @ NextPos = CHARINDEX (@ spliter, @ str, @ Pos) IF (@ NextPos = 0 OR @ NextPos is null) SELECT @ NextPos = LEN (@ str) + 1 insert into @ tb VALUES (RTRIM (LTRIM (SUBSTRING (@ str, @ Pos, @ NextPos-@ Pos )))) SELECT @ Pos = @ NextPos + 1 ENDRETURNENDGOSELECT * FROM dbo. split ('1, 2, 3, 4 ',', ') drop function [dbo]. [split]
GO

Note:

1. Only text strings can be separated !!!