34,594
社区成员
发帖
与我相关
我的任务
分享
declare @str varchar(512)
declare @pos int
set @str = '1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD'
set @pos = charindex( ' ', @str )
select @pos
declare @str varchar(512)
declare @pos int
set @str = '1=ABC 2=ABC 3=ABC 4=ABC 5=ABC 8=BCD 9=BCD'
set @pos = charindex( ' ', @str )
declare @str varchar(512)
declare @pos int
set @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 )
end
select *
from #tmp
drop table #tmp
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
*/
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
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
*/
------------------------------------------------------------------------
-- 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
------------------------------------------------------------------------
-- 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
--调用示例: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