mysql一条记录split拆多条_Sql的split，sql 语句之根据符号将一条数据拆分成多条记录...

看错误的字面意思就清楚，一般是substring的length长度有错误。

例如：select top 10 SUBSTRING (c_dept , 1 ,CHARINDEX (’,’ , c_dept)-1) from ##dd_daily

要截取c_dept字段中第一个逗号前面的字符串，如果字段里面没有逗号，substring就会出错。

可以加入case判断就不会出错。

select SUBSTRING (c_dept , 1 , case

when CHARINDEX (',' , c_dept)-1 >0 then CHARINDEX (',' , c_dept)-1

when CHARINDEX (',' , c_dept)-1 <=0 then 0

end) from ##dd_daily

select 'IP'=substring(a.UserName,b.number,charindex(',',a.UserName+',',b.number)-b.number)

from TestRows2Columns a

inner join master.dbo.spt_values b on b.number between 1 and len(a.UserName)

and substring(','+a.UserName,b.number,1)=','

where b.type='P' and Id=1

select 这个地方就是查询的字段，然后IP是自定义的名字 'IP'=substring(拆分的字段名,b.number,charindex('拆分的符号',拆分的字段名+',',b.number)-b.number)

from 你的表名 a

inner join master.dbo.spt_values b on b.number between 1 and len(拆分的字段名)

and substring(','+拆分的字段名,b.number,1)='拆分的符号'

where b.type='P' 后边可以是条件 and Id=1

SELECT

tt.id,

tt.empno,

tt.specno,

tt.rev,

tt.release_date,

tt.lastModifyDate,

tt.lastModifyName,

tt.lastModifyNo,

tt.operation,

SUBSTRING(

LTRIM(

RTRIM(tt.signdepartcode)

),

c.number,

CASE WHEN CHARINDEX(

',',

ltrim(

rtrim(tt.signdepartcode)

) + ',',

c.number

) - c.number > 0 THEN CHARINDEX(

',',

ltrim(

rtrim(tt.signdepartcode)

) + ',',

c.number

) - c.number WHEN CHARINDEX(

',',

ltrim(

rtrim(tt.signdepartcode)

) + ',',

c.number

) - c.number<= 0 THEN 0 END

) AS signdepartcode

FROM

(

/* 数据来源 */

select

*

from

aaa

) AS tt

INNER JOIN master.dbo.spt_values AS c ON c.number BETWEEN 1

AND LEN(

LTRIM(

RTRIM(tt.signdepartcode)

)

)

AND SUBSTRING(

',' + LTRIM(

RTRIM(tt.signdepartcode)

),

c.number,

1

) = ','

WHERE

(c.type = 'P')

标签：dept,tt,Sql,number,substring,CHARINDEX,split,拆分,sql

来源： https://blog.csdn.net/ftm_csdn/article/details/104657419