34,590
社区成员
发帖
与我相关
我的任务
分享
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*/
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
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*/
--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
*/