SQL里面如何将6*8*10的字符拆分成三个数字？

Question

转帖一个SQL函数
/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as 
begin
  declare @i int
  set @inputstr = rtrim(ltrim(@inputstr))
  set @i = charindex(@seprator , @inputstr)
  while @i >= 1
  begin
    insert @temp values(left(@inputstr , @i - 1))
    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
    set @i = charindex(@seprator , @inputstr)
  end
  if @inputstr <> '\'
  insert @temp values(@inputstr)
  return 
end
go--调用
declare @str as varchar(30)
set @str = '1,2,3,4,5'

Answer 1

一行转多列？SQL code
/*
标题：分拆列值
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：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

select parsename(replace('6*7*8','*','.'),3)
select parsename(replace('6*7*8','*','.'),2)
select parsename(replace('6*7*8','*','.'),1)

Answer 3

create function   [dbo].[f_split](@c varchar(Max),@split   varchar(2))   
returns   @t   table(col   varchar(max))   
as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
          insert   @t(col)   values   (substring(@c,1,charindex(@split,@c)-1))   
          set   @c   =   stuff(@c,1,charindex(@split,@c),'')   
        end   
      insert   @t(col)   values   (@c)   
      return   
end   

Answer 4

select parsename(replace('6*8*10', '*', '.'),3),
parsename(replace('6*8*10', '*', '.'),2),
parsename(replace('6*8*10', '*', '.'),1)

Answer 5

 select * from dbo.f_split('a,b,c',',')/*
col
----
a
b
c(3 行受影响)*/

Answer 6

---仅限于拆成三个的情况declare @s varchar(10)
set @s='6*8*10'
select LEFT(@s,CHARINDEX('*',@s)-1),
       SUBSTRING(@s,CHARINDEX('*',@s)+1,LEN(@s)-len(REPLACE(@s,'*',''))-1),
       REVERSE(LEFT(REVERSE(@s),CHARINDEX('*',REVERSE(@s))-1))
      

Answer 7

USE [TestWf]
GO/****** Object:  UserDefinedFunction [dbo].[f_splitSTR]    Script Date: 08/31/2010 17:45:33 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE FUNCTION [dbo].[f_splitSTR](
@s   varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(
col varchar(100))
AS
BEGIN
--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
DECLARE @t TABLE(
ID int IDENTITY,
b bit)    -- 这个只是一个辅助字段(因为要生成自增列数据, 所以要多一个字符来插入数据)
INSERT @t(
b) 
SELECT TOP 8000 
0 
FROM dbo.syscolumns A, dbo.syscolumns B INSERT @re
SELECT
-- 对于每个数据分隔符的位置, 取其之后的数据项
        -- 需要说明的是, 由于在 WHERE 条件处理时, 待分拆字符串前面增加了一个数据分隔符, 所以 ID 所代表的, 是数据项的真实开始位置, 而不是数据分隔符的位置
SUBSTRING(@s, ID, CHARINDEX(@split, @s + @split, ID) - ID)
FROM @t
WHERE CHARINDEX(@split, @split + @s, ID) = ID  -- 取每个数据项前面的数据分隔符的位置, 并处理该位置的记录
                         -- ^^^^^^^^^^^ 在待分拆的字符串前面加一个数据分隔符是为了处理第一个数据项
AND ID <= LEN(@s + 'a')      -- 仅需要处理待分拆字符串长度的那些记录
RETURN
ENDGO
--select * from dbo.f_splitSTR('asda,dads,qew',',')

Answer 8

sql中无直接的拆分函数可自己写一函数或过程实现。
用charindex定位各个*号的位置，再用substring取值
以下为简单的替换法
create proc p_split 
@str varchar(1000)
as
begin
declare @sqltext varchar(8000)
create table #tmp (fstr varchar(50))
if charindex('*',@str)>0
begin
set @sqltext='insert into #tmp values('''+
replace(@str,'*',''') insert into #tmp values (''')+''')'
exec (@sqltext)
end else
  insert into #tmp values (convert(varchar(50),@str))select * from #tmp
drop table #tmp
end

Answer 9

根据net_08我改了下，总算达到需求了
alter function [dbo].[F_Split](@c varchar(Max),@split varchar(2), @int int)
returns nvarchar(10)
as
begin
declare @string nvarchar(50)
declare @i int
set @i = 0

while(1=1)   
begin
set @i = @i + 1
if charindex(@split,@c) > 0
begin
set @string = substring(@c,1,charindex(@split,@c)-1)
set @c = stuff(@c,1,charindex(@split,@c),'')
end
else
begin
set @string = @c
end
if @i = @int
break
end
return @string
end获取时，这么写就可以了：select dbo.F_Split('5.2*8*12','*',1)
本来刚一开始使用8楼的写法，但字符串里有“.”符号，所以就没采用。