【求助】如何查询某一字段中某个字符的值

select *
from tb
where values like '%1%'
--or charindex('1',values)>0

/*
-----------------------------------
-------T-MAC ---------------------
---------------小编---------------
   ---------------love 轩--------
-----------------------------------
*/
拆分表:
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number)
from
    Tab a join master..spt_values  b
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:select
    a.COl1,b.Col2
from
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:--此法roy博客;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/
也许你要拆分

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-03-21 14:50:05
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
-- Aug  6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([ID] int,[Values] varchar(5))
insert #TB
select 1,'1,2,3' union all
select 2,'3,4' union all
select 3,'2,3'--------------开始查询--------------------------select
ID,
SUBSTRING([Values],NUMBER,CHARINDEX(',',[Values]+',',NUMBER)-NUMBER)
from #TB ,MASTER..SPT_VALUES WHERE TYPE='P' AND SUBSTRING(','+[Values],NUMBER,1)=','
----------------结果----------------------------
/*（所影响的行数为 3 行）ID
----------- -----
1           1
1           2
1           3
2           3
2           4
3           2
3           3（所影响的行数为 7 行）
*/?

什么意思??
--这样?
select
  *
from
  tb
where
  charindex(',1,2,3,',','+values+',')>0

莫非是这个?
分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-23  广东深圳)/*问题描述
tba
ID  classid   name
1     1,2,3   西服
2     2,3     中山装
3     1,3     名裤
tbb
id   classname
1     衣服
2     上衣
3     裤子我得的结果是
id   classname            name
1     衣服,上衣,裤子      西服
2     上衣，裤子          中山装
3     衣服，裤子          名裤
*/-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go--第1种方法，创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
  return stuff(@str,1,1,'')
end
go
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id          classid       name
----------- ------------- ----------
1           衣服,上衣,裤子西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
（所影响的行数为 3 行）
*/--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID          classid              name
----------- -------------------- ----------
1           衣服,上衣,裤子       西服
2           上衣,裤子            中山装
3           衣服,裤子            名裤
（所影响的行数为 3 行）
*/
drop table tba,tbb------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
goSELECT id , classname , name FROM
(
  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from
  (
    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
  ) tbc , tbb where tbc.classid = tbb.id
  ) T
)A
OUTER APPLY
(
  SELECT [classname]= STUFF(REPLACE(REPLACE((
    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from
    (
      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
    ) tbc , tbb where tbc.classid = tbb.id
  ) N
  WHERE id = A.id and name = A.name
  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by iddrop table tba,tbb/*
id          classname      name
----------- -------------- ----------
1           衣服,上衣,裤子西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
(3 行受影响)
*/

--(1)字符串分拆并统计
create table #tb
(
id int,
col varchar(50),
num int
)
insert into #tb select 1,'aa,bb,cc',10
union all select 2,'aa,aa,bb',20
union all select 3,'aa,aa,bb',20
union all select 4,'dd,ccc,c',30
union all select 5,'ddaa,ccc',40
union all select 6,'eee,ee,c',50

declare @Len int
select top 1 @Len=len(col)+1 from #tb order by len(col)
select @Len
set rowcount @Len
select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B
set rowcount 0
;with hgo as
(
   select b.id,
          number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)
    from #TT A join #tb b on substring(','+col,A.id,1)=','
)
select number,count(distinct id) [count],count(number) [number] from hgo group by number
--(2)分拆字符串求和
create table #tb
(
id int,
col varchar(50),
num int
)
insert into #tb select 1,'aa,bb,cc',10
union all select 2,'aa,aa,bb',20
union all select 3,'aa,aa,bb',20
union all select 4,'dd,ccc,c',30
union all select 5,'ddaa,ccc',40
union all select 6,'eee,ee,c',50

declare @len int
select top 1 @len=len(col)+1 from #tb order by len(col) desc

set rowcount @len
select id=identity(int,1,1) into # from dbo.syscolumns A,dbo.syscolumns B
set rowcount 0

;with hgo as
(
   select distinct data=substring(A.col,b.id,charindex(',',A.col+',',b.id)-b.id),
   A.num,b.id
   from #tb A,# b
   where substring(','+A.col,b.id,1)=','
)
select data,sum(num) from hgo group by data
--(3)合并法(函数)
create table tb1
(
  col1 varchar(10),
  col2 int
)
insert into tb1 select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3
create function dbo.FC_Str(@col1 varchar(100))
returns varchar(100)
as
begin
   declare @i varchar(100)
   set @i=''
   select @i=@i+','+cast(col2 as varchar) from tb1 where col1=@col1
   return(stuff(@i,1,1,''))

end
select col1,dbo.FC_Str(col1) from tb1 group by col1
--(4)固定行合并法
create table #tb
(
  col1 varchar(10),
  col2 int
)
insert into #tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'c',3

select col1,
       col2=cast(min(col2) as varchar)+
       case when count(*)=1 then ''
       else +','+cast(max(col2) as varchar) end from #tb
group by col1
--(5)临时表合并法
if object_id('tb') is not null drop table tb
create table tb
(
  col1 varchar(10),
  col2 int
)
insert into tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3

select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2
declare @col1 varchar(20)
declare @col2 varchar(100)
update #t1 set @col2=
                   case when @col1=col1 then @col2+ ',' +col2
                   else col2 end,
           @col1=col1,
           col2=@col2

select * from #t1

select col1,col2=max(col2) from #t1 group by col1
--(6)字符串拆分
create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
returns @R table (Col nvarchar(100))
as
begin
    declare @StrLen int
    set @StrLen=len(@Str)
    while charindex(@Split,@StrLen)>0
     begin
      insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
       set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
      end
     insert into @R values(@Str)
return
end

declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'

declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)

--(8)分拆数据到列
;declare @tb table
(
  col varchar(20)
)
insert into @tb select 'aa,bb,cc'
union all select 'AAA,BB'
union all select 'AAA'

declare @i int,@j varchar
set @j=1
select col+',' as col into #t1 from @tb--加上','
select @i=max(len(Col)-len(replace(Col,',',''))) from #t1

while @j!>@i
begin
    exec('alter table #t1 add  [Col'+@j+'] varchar(10)')
    exec('update #t1 set [Col'+@j+']=left(col,charindex('','',col)-1),col=stuff(col,1,charindex('','',col),'''') where Len(col)>1')
    set @j=@j+1
end
alter table #T1 drop column Col
select * from #t1
drop table #t1;
/*
Col1       Col2       Col3
---------- ---------- ----------
aa         bb         cc
AAA        BB         NULL
AAA        NULL       NULL

*/
--(9)字符串分拆并统计
create table #tb
(
id int,
col varchar(50),
num int
)
insert into #tb select 1,'aa,bb,cc',10
union all select 2,'aa,aa,bb',20
union all select 3,'aa,aa,bb',20
union all select 4,'dd,ccc,c',30
union all select 5,'ddaa,ccc',40
union all select 6,'eee,ee,c',50

declare @Len int
select top 1 @Len=len(col)+1 from #tb order by len(col)
select @Len
set rowcount @Len
select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B
set rowcount 0
;with hgo as
(
   select b.id,
          number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)
    from #TT A join #tb b on substring(','+col,A.id,1)=','
)
select number,count(distinct id) [count],count(number) [number] from hgo group by number
number                                             count       number
-------------------------------------------------- ----------- -----------
aa                                                 3           5
bb                                                 3           3
c                                                  2           2
cc                                                 1           1
ccc                                                2           2
dd                                                 1           1
ddaa                                               1           1
ee                                                 1           1
eee                                                1           1

(9 行受影响)
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/22/5224723.aspx

http://blog.csdn.net/ws_hgo/archive/2010/01/22/5224723.aspx

/*
标题：数据拆分1
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-20
地点：广东深圳
描述有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/--1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go--方法1)
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B--方法2)
select
    a.id,b.[value]
from
    (select id,[value]=convert(xml,'<root><v>'+replace([value],',','</v><v>')+'</v></root>') from tb)a
outer apply
    (select [value]=C.v.value('.','nvarchar(100)') from a.[value].nodes('/root/v')C(v))b--方法3)
;with tt as
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

调试易

【求助】如何查询某一字段中某个字符的值

解决方案 »