to libin_ftsafe(子陌红尘) 老兄还没有理解我得意思吧,我是说1001,1002,1003这三个字段存在同一个字段里得
將一個字符串拆分成獨立的多個字符串,比如像字符串:'a,b,c,d,e'把它拆分成五個字符串:
create table temp(aaa varchar(20)) declare @strsql as varchar(8000) select @strsql='' select @strsql='insert into temp values ('''+replace('a,b,c,d,e',',',''') insert into temp values (''')+''')' exec (@strsql) 通过中间表关联一下
Create Table MyTable(myField1 int,myField2 int) insert into mytable select 1001,2 union all select 1002,1 union all select 1003,3 ----------------------------下面的語句可找到------------------ select * from myTable where myField2=right(cast('1002' as varchar(4)),2)---------
create table mytable ( MyField1 varchar(8000), MyField2 varchar(8000) ) insert into mytable select '10011007,' , '1,' union all select '10011007,10011015,' , '2,200,' union all select '10011015,10011007,' , '200,3,' union all select '10011015,10011007,' , '200,4,' select substring(myfield1,charindex('10011007',myfield1,1),len('10011007')) as Myfield1, substring(','+Myfield2, charindex(',',','+MyField2, charindex('10011007',MyField1)%len('10011007'))+1,1) as MyField2 from mytable 10011007 1 10011007 2 10011007 3 10011007 4(所影响的行数为 4 行)
select '10011007,' MyField1 ,'1,' MyField2 into #t union select '10011007,10011015,', '2,200,' union select '10011015,10011007,' , '200,3,' union select '10011015,10011007,' , '200,4,' while exists(select * from #t where ','+MyField1 like '%,10011007,%' ) update #t set Myfield1=case when charindex(',10011007,', ','+Myfield1)=1 then '10011007' else right(Myfield1,len(Myfield1)-charindex(',',Myfield1)) end, MyField2=case when charindex(',10011007,', ','+Myfield1)=1 then substring( MyField2,1 ,charindex(',',MyField2)-1) else right(MyField2,len(MyField2)-charindex(',',MyField2)) end where ','+MyField1 like '%,10011007,%' select * from #tdrop table #t MyField1 MyField2 ------------------ -------- 10011007 1 10011007 2 10011007 3 10011007 4(所影响的行数为 4 行)会更改原表 注意使用
select substring(myfield1,charindex('10011007',myfield1,1),len('10011007')) as Myfield1, substring(','+Myfield2, charindex(',',','+MyField2, charindex('10011007',MyField1)%len('10011007'))+1, charindex(',',+MyField2, charindex('10011007',MyField1)%len('10011007')) ) as MyField2 from mytable
老兄还没有理解我得意思吧,我是说1001,1002,1003这三个字段存在同一个字段里得
create table temp(aaa varchar(20))
declare @strsql as varchar(8000)
select @strsql=''
select @strsql='insert into temp values ('''+replace('a,b,c,d,e',',',''') insert into temp values (''')+''')'
exec (@strsql) 通过中间表关联一下
insert into mytable
select 1001,2 union all select 1002,1 union all select 1003,3
----------------------------下面的語句可找到------------------
select * from myTable where myField2=right(cast('1002' as varchar(4)),2)---------
在MyTable中有两字段MyField1,MyField2,现在表数据如下
MyField1 MyField2
10011007, 1,
10011007,10011015, 2,200,
10011015,10011007, 200,3,
10011015,10011007, 200,4,现在要通过SQL语句得到一下数据【找出在字段MyField1中10011007相对应得MyField2中得数据】
MyField1 MyField2
10011007 1
10011007 2
10011007 3
10011007 4
中的1 2 3 4 是怎麼來的
(
MyField1 varchar(8000),
MyField2 varchar(8000)
)
insert into mytable
select '10011007,' , '1,' union all
select '10011007,10011015,' , '2,200,' union all
select '10011015,10011007,' , '200,3,' union all
select '10011015,10011007,' , '200,4,'
select substring(myfield1,charindex('10011007',myfield1,1),len('10011007')) as Myfield1,
substring(','+Myfield2,
charindex(',',','+MyField2,
charindex('10011007',MyField1)%len('10011007'))+1,1) as MyField2
from mytable
10011007 1
10011007 2
10011007 3
10011007 4(所影响的行数为 4 行)
select '10011007,' MyField1 ,'1,' MyField2 into #t union
select '10011007,10011015,', '2,200,' union
select '10011015,10011007,' , '200,3,' union
select '10011015,10011007,' , '200,4,' while exists(select * from #t where ','+MyField1 like '%,10011007,%' )
update #t set
Myfield1=case when charindex(',10011007,', ','+Myfield1)=1
then '10011007'
else right(Myfield1,len(Myfield1)-charindex(',',Myfield1))
end,
MyField2=case when charindex(',10011007,', ','+Myfield1)=1
then substring( MyField2,1 ,charindex(',',MyField2)-1)
else right(MyField2,len(MyField2)-charindex(',',MyField2))
end
where ','+MyField1 like '%,10011007,%'
select * from #tdrop table #t
MyField1 MyField2
------------------ --------
10011007 1
10011007 2
10011007 3
10011007 4(所影响的行数为 4 行)会更改原表 注意使用
你的方案很不错,但是你钻了一个空,如果我把3换成30你怎么做呢
如
10011007 1
10011007 2
10011007 30
10011007 4
substring(','+Myfield2,
charindex(',',','+MyField2,
charindex('10011007',MyField1)%len('10011007'))+1,
charindex(',',+MyField2,
charindex('10011007',MyField1)%len('10011007'))
)
as MyField2
from mytable
select substring(myfield1,charindex('10011007',myfield1,1),len('10011007')) as Myfield1,
left(
substring(','+Myfield2,
charindex(',',','+MyField2,
charindex('10011007',MyField1)%len('10011007'))+1,
charindex(',',+MyField2,
charindex('10011007',MyField1)%len('10011007'))
),
len(
substring(','+Myfield2,
charindex(',',','+MyField2,
charindex('10011007',MyField1)%len('10011007'))+1,
charindex(',',+MyField2,
charindex('10011007',MyField1)%len('10011007'))
)
)
-1)
as MyField2
from mytable10011007 1
10011007 2
10011007 30
10011007 4