Sql中将字符串按分割符拆分
时间：2021-07-01 10:21:17
帮助过：462人阅读
SET ANSI_NULLS
ON
2 GO
3
4 SET QUOTED_IDENTIFIER
ON
5 GO
6
7 Create FUNCTION [dbo].
[F_Split]
8 (
9 @SplitString nvarchar(
max),
--源字符串
10 @Separator nvarchar(
10)
=‘ ‘ --分隔符号，默认为空格
11 )
12 RETURNS @SplitStringsTable TABLE --输出的数据表
13 (
14 [id] int identity(
1,
1),
15 [value] nvarchar(
max)
16 )
17 AS
18 BEGIN
19 DECLARE @CurrentIndex int;
20 DECLARE @NextIndex int;
21 DECLARE @ReturnText nvarchar(
max);
22
23 SELECT @CurrentIndex=1;
24 WHILE(
@CurrentIndex<=len(
@SplitString))
25 BEGIN
26 SELECT @NextIndex=charindex(
@Separator,
@SplitString,
@CurrentIndex);
27 IF(
@NextIndex=0 OR @NextIndex IS NULL)
28 SELECT @NextIndex=len(
@SplitString)
+1;
29 SELECT @ReturnText=substring(
@SplitString,
@CurrentIndex,
@NextIndex-@CurrentIndex);
30 INSERT INTO @SplitStringsTable(
[value])
VALUES(
@ReturnText);
31 SELECT @CurrentIndex=@NextIndex+1;
32 END
33 RETURN;
34 END
35 GO
使用：
select * from F_Split(‘1,2,3,4,5‘,‘,‘)
select * from tb_SysUser u where u.ID in (select value from dbo.F_Split(‘1,2,3,4‘,‘,‘))
Sql中将字符串按分割符拆分
标签：div _id func ges into ansi max user ide
