分割

[test104]

12-0-12
要怎麼切割成

12 0 12

SELECT '12-0-12',SUBSTRING('12-0-12', 1, CHARINDEX('-', '12-0-12') - 1)

[吉普赛的歌]

两种方法： 1. 建立分割表值函数：

--1. 创建fn_Split函数. ( 切分字符串， 返回一个列名为id的表 )  
IF EXISTS(
       SELECT *
       FROM   dbo.sysobjects
       WHERE  id = OBJECT_ID('fn_Split')
              AND (TYPE = 'FN' OR TYPE = 'TF' OR TYPE = 'IF')
   )
    DROP FUNCTION fn_Split  
GO  
  
CREATE FUNCTION [dbo].[fn_Split]
(
	@str           VARCHAR(MAX),
	@separator     VARCHAR(10)
)
RETURNS TABLE
AS
	RETURN 
	(
	    --Example:  SELECT id FROM fn_Split('a,b,d,c',',')  
	    SELECT B.id
	    FROM   (
	               (
	               	   --A　的作用只是生成 '<v>a</v><v>b</v><v>d</v><v>c</v>' 的XML格式的数据, 提供数据源 
	                   SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')
	               ) A 
	               OUTER APPLY
	               (
	               	   --B 的作用是将A中的 XML 数据的值枚举出来转换成行
	                   SELECT id = N.v.value('.', 'varchar(100)') FROM   A.[value].nodes('/v') N(v)
	               ) B
	           )
	)
GO

2. 如果用得不多， 而且数量不超过四个， 可以：

DECLARE @s NVARCHAR(50)
SET @s='12-0-123'
SELECT 
PARSENAME(REPLACE(@s,'-','.'),1) AS r1
,PARSENAME(REPLACE(@s,'-','.'),2) AS r2
,PARSENAME(REPLACE(@s,'-','.'),3) AS r3
/*
r1  r2  r3
123	0	12
*/

[中国风]

SELECT @str,SUBSTRING(@str, 1, CHARINDEX('-', '12-0-12') - 1) 
,SUBSTRING(@str, CHARINDEX('-', '12-0-12')+1, CHARINDEX('-', '12-0-12',CHARINDEX('-', '12-0-12')+1) -CHARINDEX('-', '12-0-12')-1)
,RIGHT(@str,CHARINDEX('-',REVERSE(@str))-1)
/*
(无列名)	(无列名)	(无列名)	(无列名)
12-0-12	12	0	12*/

[文盲老顾]

自己创建个split函数是最省事的办法，毕竟这个方法比较常用

CREATE FUNCTION [dbo].[SplitStr]
(	
	@string nvarchar(max), 
	@symbol nvarchar(10)
)
RETURNS @table TABLE(id int identity,value nvarchar(max))
AS
begin 
	DECLARE @splitlen int
    SET @splitlen=LEN(@symbol)-1
    WHILE CHARINDEX(@symbol,@string)>0
    BEGIN
        INSERT @table(value) VALUES(LEFT(@string,CHARINDEX(@symbol,@string)-1))
        SET @string=STUFF(@string,1,CHARINDEX(@symbol,@string)+@splitlen,'')
    END
    INSERT @table(value) VALUES(@string)
    return
end

declare @c varchar(max)
set @c = '15-5-778-8'
select @c,* from caigou_2017_query.dbo.SplitStr(@c,'-') a
pivot(
	max(value) for id in ([1],[2],[3],[4],[5],[6])
) b

[中国风]

OR e.g.

DECLARE @str VARCHAR(20)='12-023-13'
SELECT 
MAX(CASE WHEN RN=1 THEN str1 END )
,MAX(CASE WHEN RN=2 THEN str1 END )
,MAX(CASE WHEN RN=3 THEN str1 END )
FROM 
(SELECT ROW_NUMBER()OVER(ORDER BY RAND()) AS RN, SUBSTRING(@str,a.number,CHARINDEX('-',@str+'-',a.number)-a.number) AS str1
FROM master..spt_values AS a
WHERE a.type='P' AND CHARINDEX('-','-'+@str,a.number)=a.number) AS t
/*
(无列名)	(无列名)	(无列名)
12	023	13*/