T-SQL 分割字符串问题汇总

  T-SQL 分割字符串问题

1) declare @string varchar(1000)

declare @xh char(10)

declare @sh varchar(13)

set @string='l,ll,xl,ml,xxl'

set @string='select * into ##tmp from (select '''+replace(@string,',','''as str union all select ''')+''') a'

exec(@string)

print @string

 

select * from ##tmp

drop table ##tmp

这里出来的结果为

str

-------

l

ll

xl

ml

xxl

 

2) 分割字符串问题的补充解法

1 ) select top 1000 identity(int,1,1) as col,0 aa into #temp from

(select top 100 * from sysobjects)a, (select top 100 * from sysobjects)b,

(select top 100 * from sysobjects)c

 

declare @a table (id int,string varchar(8000))

insert @a select 1 ,'a,b,c,sd,dfsdfg' union select 2, 'a,n,sdf,we,t' union select 3, 's,df,df'

 

select id,substring(','+string+',',col+1,charindex(',',','+string+',',col+1)-(col+1))

from @a a,#temp b

where len(string+',')-len(replace(string,',',''))>=b.col

order by id

 

2）Create table #Result(Item varchar(30))

Declare @List varchar(200)

Declare @SQLString varchar(3000)

Set @List='1→1, 7→1, 8→1, 9→1, 10→1, 11→1, 12→1'

set @SQLString='INSERT INTO #Result VALUES ('''+REPLACE(@List,', ',''') INSERT INTO #Result VALUES (''') +''')'

EXEC (@SQLString)

SELECT * From #Result

 

3) 得到用','分割的子串

 

create function getstrofindex (@str varchar(8000),@index int =0)

returns varchar(8000)

as

begin

 declare @str_return varchar(8000)

 declare @start int

 declare @next int

 declare @location int

 select @start =1

 select @next =1

 select @location = charindex(',',@str,@start)

 while (@location <>0 and @index > @next )

 begin

    select @start = @location +1

    select @location = charindex(',',@str,@start)

    select @next =@next +1

 end

 if @location =0 select @location =len(@str)+1

 select @str_return = substring(@str,@start,@location -@start)

 if (@index <> @next ) select @str_return = ''

 return @str_return

end

go

select dbo.getstrofindex('aa vv,cc ee, sdfasdfasf',3)

 

4) 关于字符串分割字符串导入到对应表中

 

T_Crt where

TypeID      Customer                                          

0           CNFZ

1           CNFZ

4           CNFZ

 

T_CustomerType

ID          LocalName    

0           发货人                                           

1           委托人                                           

2           船公司                                           

3           收货人                                           

4           通知人

 

T_Customers

CustomerType                 Alias         

|发货人|委托人|通知人        CNPZ

 

要得到

CNPZ     发货人

CNPZ     委托人

CNPZ     通知人

大力       发货人

大力       委托人

大力       通知人

 

Answer

select top 8000 identity(int,1,1) id into #dali from sysobjects a,sysobjects b

declare @dali table (a varchar(100),b varchar(1000))

insert @dali values ('CNPZ','|发货人|委托人|通知人')

insert @dali values ('大力','|发货人|委托人|通知人')

select a,substring(b,id+1,charindex('|',b+'|',id+1)-id-1) LocalName

from @dali aa,#dali bb

where substring(b,id,8000) like '|_%'

order by a,id

 

问:根据第三种表格怎么导出第一种表格

 

create table #t (CustomerType varchar(100),Alias varchar(1000))

insert #t values ('|发货人|委托人|通知人', 'CNPZ')

create table #T_CustomerType(ID int, LocalName varchar(10))

insert #T_CustomerType values (0, '发货人')    insert #T_CustomerType values (1, '委托人')                                         

insert #T_CustomerType values (2, '船公司')    insert #T_CustomerType values (3, '收货人')                                         

insert #T_CustomerType values (4, '通知人')                                         

 

select A.id, B.alias from #T_CustomerType as A left join #t as B

 on charindex(A.LocalName, B.CustomerType) > 0 where B.alias is not null

 

5) 分割字符串替代求值

表A

ID       操作码名    分类

01       报废         1

02       重焊         2

03       换新         3

04       清洗         1

 

 

表B

单号     操作ID     时间

A0001     04       2003-6-21

A0002     01,02    2003-6-21

A0003     02,04    2003-6-21

 

表B中操作ID取值于表Ａ的ＩＤ字段，有多个操作的用","隔开

 

查询出来的结果要从多个操作ID中选一个分类数最大的作为一条记录的分类，结果如：

单号     操作ID     时间          分类

A0001     04       2003-6-21        1

A0002     01,02    2003-6-21        2   

A0003     02,04    2003-6-21        2

 

Answer

Select 单号,操作ID,时间,分类=(Select Max(a.分类) From 表A as a

            where CharIndex(a.ID,b.操作ID)>0) From 表B as b

 

6）N要连续

select top 8000 identity(int,1,1) as N into numtab from

(select top 100 id=1 from sysobjects) as a,

(select top 100 id=1 from sysobjects) as b,

(select top 100 id=1 from sysobjects) as c

 

---------------------------------------------------------------------

declare @a table (id int,string varchar(8000))

insert @a select 1 ,'a,b,c,sd,dfsdfg'

union select 2, 'a,n,sdf,we,t'

union select 3, 's,df,df'

 

  

select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1))

from @a a,numtab b

where substring(','+string+',',N,8000) like ',_%'

order by id,N