求一sql

Question

NTEXT 类型字段 shifts内容：
1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD
一共有多少不一定如何找出某个数字对应的值 ，如 1 ABC可以把NTEXT型转化为varchar  convert但是 用charindex (' ',) 中得不到空格的位置求解

Answer 1

先拆分,按如下,然后用charindex()+substring./*
标题：分拆列值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, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], 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
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)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

Answer 2

charindex 可以得到空格的位置，例如
select charindex (' ','a b')/*
----------- 
2（所影响的行数为 1 行）
/

Answer 3

怎么拆呢，空格不能在charindex中，不知道怎么拆

Answer 4

--调用示例：MySplit('123|456|678','|',2)
CREATE   FUNCTION [MySplit](@str1 as varchar(8000),@str2 as varchar(1),@subnum int)
RETURNS varchar(200)Begin
declare @Restr varchar(200),@x int,@y int
set @y=0
while @subnum>0
begin
set @subnum=@subnum-1
set @y=CHARINDEX(@str2,@str1,@y+1)
if(@subnum=1)
set @x=@y
end
if(@x is null)
set @x=0
if(@y=0)
set @y=len(@str1)+1
set @Restr=SUBSTRING(@str1,@x+1,@y-@x-1)
return (@Restr)
end

Answer 5

--------------------------------------------------------------------------       Author : navy887(草根)--       用途：分隔字符串的存储过程--       使用方法：EXEC DivideString '1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD',',','表名'--       转载请注明出处------------------------------------------------------------------------CREATE PROCEDURE DivideString
(
@String NVARCHAR(1210),  -- 要分隔的字符串
@SPLITCHAR NVARCHAR(10) = ' ', -- 默认分隔字符
@TableName NVARCHAR(30) = 'table'  --默认表名
)
AS
DECLARE @L INT -- 第一个分隔字符的位置
DECLARE @S INT -- 第二个分隔字符的位置
SET @L = 0 
SET @S = CHARINDEX(@SPLITCHAR, @String, @L)WHILE @L <= LEN(@String)
BEGIN
 DECLARE @ColName NVARCHAR(50) 
 IF @S = 0 SET @S = LEN(@String) + 1 -- 如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一
 SET @ColName = SUBSTRING(@String, @L, @S - @L) -- 取值
 SET @L = @S + 1
 SET @S = CHARINDEX(@SPLITCHAR, @String, @L)
 IF LTRIM(RTRIM(@ColName)) = '' CONTINUE -- 如果是空字符串就跳过
 DECLARE @SQL NVARCHAR(1000)
 SET @SQL ='INSERT INTO ' + @TableName + '(tcname) select ''' +@ColName+'''' --插入表的语句，根据自己需求修改
 --EXEC (@SQL)
 Print @sql
END本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/navy887/archive/2009/10/16/4680239.aspx

Answer 6

--------------------------------------------------------------------------       Author : navy887(草根)--       用途：分隔字符串的存储过程--       使用方法：EXEC DivideString '1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD',' ','表名'--       转载请注明出处------------------------------------------------------------------------CREATE PROCEDURE DivideString
(
@String NVARCHAR(1210),  -- 要分隔的字符串
@SPLITCHAR NVARCHAR(10) = ' ', -- 默认分隔字符
@TableName NVARCHAR(30) = 'table'  --默认表名
)
AS
DECLARE @L INT -- 第一个分隔字符的位置
DECLARE @S INT -- 第二个分隔字符的位置
SET @L = 0 
SET @S = CHARINDEX(@SPLITCHAR, @String, @L)WHILE @L <= LEN(@String)
BEGIN
 DECLARE @ColName NVARCHAR(50) 
 IF @S = 0 SET @S = LEN(@String) + 1 -- 如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一
 SET @ColName = SUBSTRING(@String, @L, @S - @L) -- 取值
 SET @L = @S + 1
 SET @S = CHARINDEX(@SPLITCHAR, @String, @L)
 IF LTRIM(RTRIM(@ColName)) = '' CONTINUE -- 如果是空字符串就跳过
 DECLARE @SQL NVARCHAR(1000)
 SET @SQL ='INSERT INTO ' + @TableName + '(tcname) select ''' +@ColName+'''' --插入表的语句，根据自己需求修改
 EXEC (@SQL)
 --Print @sql
END

Answer 7

你这个也可以程序中直接用SPLIT拆分

Answer 8

2=BC01  3=BC01  4=BC01  5=BC01  6=BC01  7=BC01  9=BC02  10=BC02  11=BC02  12=BC02  13=BC02为什么我这个用就没用呢select charindex(' ',convert(varchar(8000),shifts)) from Shifts_Rule where shift_id='GL003'结果=0 

Answer 9

create table #(id int identity ,col ntext)
insert into # select '1=ABC 2=ABC 3=ABC   4=ABC 5=ABC 8=BCD 98=BCD 101=ADFADSF '
select * from #--自定义函数
--SQL Server Split函数
--Author:zc_0101 
--说明：
--支持分割符多字节
--使用方法 
--Select * FROM DBO.F_SQLSERVER_SPLIT('1203401230105045','0')    
--select * from DBO.F_SQLSERVER_SPLIT('abc1234a12348991234','1234') 
--Select * from DBO.F_SQLSERVER_SPLIT('ABC',',')   
 CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str varchar(8000),@split_str varchar(100))    
RETURNS  @tmp TABLE(        
    ID          inT     IDENTITY PRIMARY KEY,      
    short_str   varchar(8000)    
)    
AS   
BEGIN   
    DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int   
    SET @split_str_length = LEN(@split_str)    
    IF CHARINDEX(@split_str,@Long_str)=1 
         SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
    ELSE
         SET @long_str_Tmp=@Long_str
    IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1    
        SET @long_str_Tmp=@long_str_Tmp+@split_str    
    ELSE   
        SET @long_str_Tmp=@long_str_Tmp    
    IF CHARINDEX(@split_str,@long_str_Tmp)=0
        Insert INTO @tmp select @long_str_Tmp 
    ELSE
        BEGIN
            WHILE CHARINDEX(@split_str,@long_str_Tmp)>0    
                BEGIN   
                    SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)    
                    DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int   
                    SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)    
                    SET @split_str_Position_END = LEN(@short_str)+@split_str_length    
                    SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
                    IF @short_str<>'' Insert INTO @tmp select @short_str    
                END           
        END
    RETURN     
END --正式查询--
declare @num int
set @num=1
select replace(short_str,ltrim(@num)+'=','') from dbo.f_sqlserver_split((select col from # where id=1),' ') 
where charindex(ltrim(@num)+'=',short_str)=1  --result--
/*
ABC
*/

Answer 10

搞错了，原来数据库里是这样的
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1=BC01
2=BC01
3=BC01
4=BC01
5=BC01
6=BC01
7=BC01
shift_id=GL003（所影响的行数为 1 行）这个该用什么来做的

Answer 11

直接运行这个就可以了
DECLARE @String NVARCHAR(1210)  
DECLARE @SPLITCHAR NVARCHAR(10) 
DECLARE @L INT
DECLARE @S INT set @String='1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD'
set @SPLITCHAR=' '
SET @L = 0 
SET @S = CHARINDEX(@SPLITCHAR, @String, @L)WHILE @L <= LEN(@String)
BEGIN
 DECLARE @ColName NVARCHAR(50) 
 IF @S = 0 SET @S = LEN(@String) + 1 
 SET @ColName = SUBSTRING(@String, @L, @S - @L) 
 SET @L = @S + 1
 SET @S = CHARINDEX(@SPLITCHAR, @String, @L)
 IF LTRIM(RTRIM(@ColName)) = '' CONTINUE 
 DECLARE @SQL NVARCHAR(1000)
 Print replace(@ColName,'=',' ')
END

Answer 12

这样的话直接用replace函数替换就可以了。

Answer 13

是不是想得到这种结果？DECLARE @String NVARCHAR(1210)  
DECLARE @SPLITCHAR NVARCHAR(10) 
DECLARE @L INT
DECLARE @S INT set @String='1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD'
set @SPLITCHAR=' '
SET @L = 0 
SET @S = CHARINDEX(@SPLITCHAR, @String, @L)WHILE @L <= LEN(@String)
BEGIN
 DECLARE @ColName NVARCHAR(50) 
 IF @S = 0 SET @S = LEN(@String) + 1 
 SET @ColName = SUBSTRING(@String, @L, @S - @L) 
 SET @L = @S + 1
 SET @S = CHARINDEX(@SPLITCHAR, @String, @L)
 IF LTRIM(RTRIM(@ColName)) = '' CONTINUE 
 DECLARE @SQL NVARCHAR(1000)
 Print @ColName
END/*
结果：
1=ABC
2=ABC
3=ABC
4=ABC
5=ABC
8=BCD
9=BCD
*/

Answer 14

declare @str varchar(512)
declare @pos intset @str = '1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD'set @str = replace(@str, ' ', ',' )
create table #tmp(id decimal identity not null,
            strval varchar(512))set @pos = charindex(  ',', @str )while @pos >0 
begin
  insert into #tmp(strval)
       select substring( @str, 1, @pos - 1 )  set @str = substring( @str, @pos + 1, len( @str ) - @pos  )
  set @pos = charindex(  ',', @str )
endselect *
from #tmpdrop table #tmp
id                   strval                                                                                                                                                                                                                                                           
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1                    1=ABC
2                    2=ABC
3                    3=ABC
4                    4=ABC
5                    5=ABC
6                    8=BCD（所影响的行数为 6 行）

Answer 15

另外,楼主,charindex是可以得到空格的位置的.
declare @str varchar(512)
declare @pos intset @str = '1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD'set @pos = charindex(  ' ', @str )
            
----------- 
6（所影响的行数为 1 行）

Answer 16

另外,楼主,charindex是可以得到空格的位置的.declare @str varchar(512)
declare @pos intset @str = '1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD'set @pos = charindex(  ' ', @str )select @pos
            
----------- 
6（所影响的行数为 1 行）