반응형
T-SQL 분할 문자열
쉼표로 분할해야 하는 문자열이 포함된 SQL Server 2008 R2 열이 있습니다.Stack Overflow에서 많은 답변을 보았지만 R2에서는 모두 동작하지 않습니다.모든 분할 함수 예제에 대한 선택 권한이 있는지 확인했습니다.아무쪼록 잘 부탁드립니다.
이전에 이 SQL을 사용한 적이 있는데, 이 SQL은 고객에게 도움이 될 수 있습니다.-
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
사용방법:-
SELECT * FROM dbo.splitstring('91,12,65,78,56,789')
재귀적인 CTE나 루프 대신 보다 세트 베이스의 어프로치를 고려해 본 사람이 있습니까?이 함수는 SQL Server 2008과 쉼표를 구분자로 하는 질문에 대해 작성되었습니다.SQL Server 2016 이상(및 호환성 수준 130 이상)에서는 이 옵션이 더 좋습니다.
CREATE FUNCTION dbo.SplitString
(
@List nvarchar(max),
@Delim nvarchar(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns) AS x WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], DATALENGTH(@Delim)/2) = @Delim
) AS y
);
GO
의 길이가 <, <=는 수><=는 행의 수>를 합니다.sys.all_columns 인치 (9,980 인치)modelSQL Server 2017의 경우, 자체 사용자 데이터베이스에서 훨씬 더 높음), 자체 번호표를 작성하는 등의 다른 방법을 사용하여 숫자를 도출할 수 있습니다.시스템 테이블을 사용할 수 없거나 자체 테이블을 만들 수 없는 경우 재귀 CTE를 사용할 수도 있습니다.
CREATE FUNCTION dbo.SplitString
(
@List nvarchar(max),
@Delim nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1
FROM n WHERE n <= LEN(@List))
SELECT [Value] = SUBSTRING(@List, n,
CHARINDEX(@Delim, @List + @Delim, n) - n)
FROM n WHERE n <= LEN(@List)
AND SUBSTRING(@Delim + @List, n, DATALENGTH(@Delim)/2) = @Delim
);
GO
'.OPTION (MAXRECURSION 0) (오류)MAXRECURSION <longest possible string length if < 32768>를 외부 쿼리로 전송하여 스트링의 재귀가 100자 이상인 오류를 방지합니다.그것도 좋은 대안이 아닌 경우는, 코멘트에 기재되어 있는 이 답변을 참조해 주세요.순서 있는 스플릿 문자열 함수가 필요한 경우는 이 답변을 참조해 주세요.
는 ('')로 해야 NCHAR(<=1228) 를 조사 그 이유를 조사 중입니다.)
분할 함수에 대한 자세한 내용, 루프 및 재귀 CTE가 확장되지 않는 이유(및 증명) 및 애플리케이션 계층에서 나오는 문자열을 분할하는 경우 더 나은 대안:
마지막으로 SQL Server 2016에서는 문자열 분할 기능을 도입했습니다.
select * From STRING_SPLIT ('a,b', ',') cs
, Talie, while loop, 이 때문에 날아가 버렸다.STRING_SPLIT★★★★★★ 。
퍼포먼스 비교에 관한 훌륭한 기사: 퍼포먼스 서프라이즈와 전제 조건: STRING_SPLIT
은 '이렇게'를 사용하는 입니다.XML맷합니니다다
1. 테이블이 없는 행으로의 문자열 변환
쿼리
DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','
SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
결과
x---------x
| Value |
x---------x
| String1 |
| String2 |
| String3 |
x---------x
2. CSV 행마다 ID가 있는 테이블에서 행으로 변환
소스 테이블
x-----x--------------------------x
| Id | Value |
x-----x--------------------------x
| 1 | String1,String2,String3 |
| 2 | String4,String5,String6 |
x-----x--------------------------x
쿼리
-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','
SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
FROM TABLENAME
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
결과
x-----x----------x
| Id | Value |
x-----x----------x
| 1 | String1 |
| 1 | String2 |
| 1 | String3 |
| 2 | String4 |
| 2 | String5 |
| 2 | String6 |
x-----x----------x
난 그 문제를 빨리 해결할 방법이 필요했어+4우편번호에서요.
UPDATE #Emails
SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1))
WHERE ZIPCode LIKE '%-%'
프로시저가 없습니다...UDF는 없습니다. 필요한 일을 하는 아주 작은 인라인 명령어 하나뿐입니다.화려하지도 않고 우아하지도 않아요.
필요에 따라서 딜리미터를 변경하는 등, 모든 경우에 사용할 수 있습니다.
치환하면
WHILE CHARINDEX(',', @stringToSplit) > 0
와 함께
WHILE LEN(@stringToSplit) > 0
while 루프 후에 마지막 삽입을 제거할 수 있습니다!
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE LEN(@stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
if @pos = 0
SELECT @pos = LEN(@stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
RETURN
END
XML 요소에서 자주 사용되는 접근 방식은 금지된 문자의 경우 중단됩니다.이것은 세미콜론을 딜리미터로 하여도 임의의 종류의 문자와 함께 이 메서드를 사용하는 방법입니다.
SELECT SomeString AS [*] FOR XML PATH('')모든 금지된 문자를 제대로 탈출시킬 수 있도록 말이야그렇기 때문에 딜리미터를 매직 값으로 대체하여 트러블을 회피합니다.;딜리미터로 지정합니다.
DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX))
INSERT INTO @Dummy VALUES
(1,N'A&B;C;D;E, F')
,(2,N'"C" & ''D'';<C>;D;E, F');
DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")!
WITH Casted AS
(
SELECT *
,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' AS XML) AS SplitMe
FROM @Dummy
)
SELECT Casted.ID
,x.value(N'.',N'nvarchar(max)') AS Part
FROM Casted
CROSS APPLY SplitMe.nodes(N'/x') AS A(x)
결과
ID Part
1 A&B
1 C
1 D
1 E, F
2 "C" & 'D'
2 <C>
2 D
2 E, F
어떤 종류의 루프잉(반복)을 사용하는 문자열 분할 함수는 모두 성능이 저하됩니다.이러한 솔루션은 세트 기반 솔루션으로 대체해야 합니다.
이 코드는 훌륭하게 실행됩니다.
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
);
GO
최근에 이런 걸 써야 했어요.제가 생각해낸 해결책은 이렇습니다.모든 딜리미터 문자열에 대해 일반화되어 있으며, 조금 더 나은 성능을 발휘할 수 있을 것으로 생각합니다.
CREATE FUNCTION [dbo].[SplitString]
( @string nvarchar(4000)
, @delim nvarchar(100) )
RETURNS
@result TABLE
( [Value] nvarchar(4000) NOT NULL
, [Index] int NOT NULL )
AS
BEGIN
DECLARE @str nvarchar(4000)
, @pos int
, @prv int = 1
SELECT @pos = CHARINDEX(@delim, @string)
WHILE @pos > 0
BEGIN
SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
INSERT INTO @result SELECT @str, @prv
SELECT @prv = @pos + LEN(@delim)
, @pos = CHARINDEX(@delim, @string, @pos + 1)
END
INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
RETURN
END
최소 코드의 일반적인 케이스에 대한 빠른 애드혹솔루션이 필요한 경우 이 재귀적인 CTE 2라인에서 해결합니다.
DECLARE @s VARCHAR(200) = ',1,2,,3,,,4,,,,5,'
;WITH
a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @s, j + 1) FROM a WHERE j > i),
b AS (SELECT SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b
하거나 위의 할 수 .b추가 표현에 사용할 수 있도록 다른 항목과 함께 사용합니다.
편집(Shnugo에 의한)
카운터를 추가하면 List:와 함께 위치 인덱스를 얻을 수 있습니다.
DECLARE @s VARCHAR(200) = '1,2333,344,4'
;WITH
a AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @s, j+1) FROM a WHERE j > i),
b AS (SELECT n, SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b;
그 결과:
n s
1 1
2 2333
3 344
4 4
값을 요소(M)로 정리하여 xml 루트를 사용합니다.
declare @v nvarchar(max) = '100,201,abcde'
select
a.value('.', 'varchar(max)')
from
(select cast('<M>' + REPLACE(@v, ',', '</M><M>') + '</M>' AS XML) as col) as A
CROSS APPLY A.col.nodes ('/M') AS Split(a)
CTE를 사용한 솔루션.필요한 사람이 있다면 (나 말고 누가 필요했던지, 그래서 내가 쓴 거야)
declare @StringToSplit varchar(100) = 'Test1,Test2,Test3';
declare @SplitChar varchar(10) = ',';
with StringToSplit as (
select
ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head
, substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail
union all
select
ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head
, substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail
from StringToSplit
where charindex( @SplitChar, Tail ) > 0
union all
select
ltrim( rtrim( Tail ) ) Head
, '' Tail
from StringToSplit
where charindex( @SplitChar, Tail ) = 0
and len( Tail ) > 0
)
select Head from StringToSplit
이건 좀 더 좁게 맞춘 거예요.이 경우 보통 콤마로 구분된 고유 ID 목록(INT 또는 BIGINT)이 있습니다.이것은 INT 또는 BIGINT의 프라이머리 키를 가진 다른 테이블에 대한 내부 결합으로 사용할 테이블로 캐스팅하고 싶습니다.가장 효율적인 결합을 위해 인라인 테이블 값 함수를 반환하고 싶습니다.
사용 예는 다음과 같습니다.
DECLARE @IDs VARCHAR(1000);
SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,';
SELECT me.Value
FROM dbo.MyEnum me
INNER JOIN dbo.GetIntIdsTableFromDelimitedString(@IDs) ids ON me.PrimaryKey = ids.ID
저는 http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html,에서 아이디어를 훔쳐서 인라인 테이블 밸류 및 INT로 캐스팅했습니다.
create function dbo.GetIntIDTableFromDelimitedString
(
@IDs VARCHAR(1000) --this parameter must start and end with a comma, eg ',123,456,'
--all items in list must be perfectly formatted or function will error
)
RETURNS TABLE AS
RETURN
SELECT
CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID
FROM
[master].[dbo].[spt_values] Nums
WHERE Nums.Type = 'P'
AND Nums.number BETWEEN 1 AND DATALENGTH(@IDs)
AND SUBSTRING(@IDs,Nums.number,1) = ','
AND CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number;
GO
여기에 올바른 버전이 있습니다만, 후행 콤마가 있는 경우에 대비하여 약간의 폴트 톨러런스를 추가하여 함수가 아닌 더 큰 코드의 일부로 사용할 수 있도록 하는 것이 좋다고 생각했습니다.한 번만 사용하고 기능이 필요 없는 경우를 대비해서.이것은 정수(필요한 부분)에 대한 것이므로 데이터 유형을 변경해야 할 수도 있습니다.
DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'
--SELECT @StringToSeperate IDs INTO #Test
DROP TABLE #IDs
CREATE TABLE #IDs (ID int)
DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)
--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
SELECT @Position = CHARINDEX(',', @StringToSeperate)
SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)
INSERT INTO #IDs
SELECT @CommaSeperatedValue
SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)
END
--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
INSERT INTO #IDs
SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END
SELECT * FROM #IDs
+Andy Robinson의 함수를 조금 수정했습니다.이제 반환 테이블에서 필요한 부품만 선택할 수 있습니다.
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
DECLARE @orderNum INT
SET @orderNum=0
WHILE CHARINDEX('.', @stringToSplit) > 0
BEGIN
SELECT @orderNum=@orderNum+1;
SELECT @pos = CHARINDEX('.', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @orderNum,@name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
SELECT @orderNum=@orderNum+1;
INSERT INTO @returnList
SELECT @orderNum, @stringToSplit
RETURN
END
Usage:
SELECT Name FROM dbo.splitstring('ELIS.YD.CRP1.1.CBA.MDSP.T389.BT') WHERE numOrder=5
시뮬레이션
DECLARE @String varchar(100) = '11,21,84,85,87'
SELECT * FROM TB_PAPEL WHERE CD_PAPEL IN (SELECT value FROM STRING_SPLIT(@String, ','))
-- EQUIVALENTE
SELECT * FROM TB_PAPEL WHERE CD_PAPEL IN (11,21,84,85,87)
위의 투고를 간단하게 개작한 patindex를 사용하여 패턴으로 분할할 수 있는 버전입니다.여러 개의 구분 문자를 포함하는 문자열을 분할해야 하는 경우가 있었습니다.
alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE PATINDEX(@splitPattern, @stringToSplit) > 0
BEGIN
SELECT @pos = PATINDEX(@splitPattern, @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%');
결과는 다음과 같다
stringa stringb x y z
개인적으로는 이 기능을 사용합니다.
ALTER FUNCTION [dbo].[CUST_SplitString]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
요청하신 더블 스플리터(두 글자 분할 가능)를 개발했습니다.문자열 분할과 관련된 쿼리에서 가장 많이 참조되는 스레드 값일 수 있습니다.
CREATE FUNCTION uft_DoubleSplitter
(
-- Add the parameters for the function here
@String VARCHAR(4000),
@Splitter1 CHAR,
@Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
MId INT,
SValue VARCHAR(4000))
SET @String = @String+@Splitter1
WHILE CHARINDEX(@Splitter1, @String) > 0
BEGIN
DECLARE @WorkingString VARCHAR(4000) = NULL
SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
--Print @workingString
INSERT INTO @FResult
SELECT CASE
WHEN @WorkingString = '' THEN NULL
ELSE @WorkingString
END
SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))
END
IF ISNULL(@Splitter2, '') != ''
BEGIN
DECLARE @OStartLoop INT
DECLARE @OEndLoop INT
SELECT @OStartLoop = MIN(Id),
@OEndLoop = MAX(Id)
FROM @FResult
WHILE @OStartLoop <= @OEndLoop
BEGIN
DECLARE @iString VARCHAR(4000)
DECLARE @iMId INT
SELECT @iString = SValue+@Splitter2,
@iMId = Id
FROM @FResult
WHERE Id = @OStartLoop
WHILE CHARINDEX(@Splitter2, @iString) > 0
BEGIN
DECLARE @iWorkingString VARCHAR(4000) = NULL
SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)
INSERT INTO @SResult
SELECT @iMId,
CASE
WHEN @iWorkingString = '' THEN NULL
ELSE @iWorkingString
END
SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))
END
SET @OStartLoop = @OStartLoop + 1
END
INSERT INTO @Result
SELECT MId AS PrimarySplitID,
ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
SValue
FROM @SResult
END
ELSE
BEGIN
INSERT INTO @Result
SELECT Id AS PrimarySplitID,
NULL AS SecondarySplitID,
SValue
FROM @FResult
END
RETURN
사용방법:
--FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)
--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')
사용 가능성(각 분할의 두 번째 값 가져오기):
SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2
재귀적 cte 기반 솔루션
declare @T table (iden int identity, col1 varchar(100));
insert into @T(col1) values
('ROOT/South America/Lima/Test/Test2')
, ('ROOT/South America/Peru/Test/Test2')
, ('ROOT//South America/Venuzuala ')
, ('RtT/South America / ')
, ('ROOT/South Americas// ');
declare @split char(1) = '/';
select @split as split;
with cte as
( select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end as col1, 0 as pos , 1 as cnt
from @T t
union all
select t.iden, t.col1 , charindex(@split, t.col1, t.pos + 1), cnt + 1
from cte t
where charindex(@split, t.col1, t.pos + 1) > 0
)
select t1.*, t2.pos, t2.cnt
, ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo
from cte t1
join cte t2
on t2.iden = t1.iden
and t2.cnt = t1.cnt+1
and t2.pos > t1.pos
order by t1.iden, t1.cnt;
@AviG에 대해 말하자면, 이것은 그가 모든 토큰을 완전히 반환하도록 고안한 버그프리 버전의 함수입니다.
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TF' AND name = 'TF_SplitString')
DROP FUNCTION [dbo].[TF_SplitString]
GO
-- =============================================
-- Author: AviG
-- Amendments: Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe
-- Description: Tabel valued function that Breaks the delimeted string by given delimeter and returns a tabel having split results
-- Usage
-- select * from [dbo].[TF_SplitString]('token1,token2,,,,,,,,token969',',')
-- 969 items should be returned
-- select * from [dbo].[TF_SplitString]('4672978261,4672978255',',')
-- 2 items should be returned
-- =============================================
CREATE FUNCTION dbo.TF_SplitString
( @stringToSplit VARCHAR(MAX) ,
@delimeter char = ','
)
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE LEN(@stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(@delimeter, @stringToSplit)
if @pos = 0
BEGIN
SELECT @pos = LEN(@stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos)
END
else
BEGIN
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
END
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
RETURN
END
이것은 Andy Robertson의 대답에 근거한 것으로, 쉼표 이외의 딜리미터가 필요했습니다.
CREATE FUNCTION dbo.splitstring ( @stringToSplit nvarchar(MAX), @delim nvarchar(max))
RETURNS
@returnList TABLE ([value] [nvarchar] (MAX))
AS
BEGIN
DECLARE @value NVARCHAR(max)
DECLARE @pos INT
WHILE CHARINDEX(@delim, @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(@delim, @stringToSplit)
SELECT @value = SUBSTRING(@stringToSplit, 1, @pos - 1)
INSERT INTO @returnList
SELECT @value
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + LEN(@delim), LEN(@stringToSplit) - @pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
GO
사용방법:
SELECT * FROM dbo.splitstring('test1 test2 test3', ' ');
(SQL Server 2008 R2에서 테스트 완료)
EDIT: 올바른 테스트 코드
ALTER FUNCTION [dbo].func_split_string
(
@input as varchar(max),
@delimiter as varchar(10) = ";"
)
RETURNS @result TABLE
(
id smallint identity(1,1),
csv_value varchar(max) not null
)
AS
BEGIN
DECLARE @pos AS INT;
DECLARE @string AS VARCHAR(MAX) = '';
WHILE LEN(@input) > 0
BEGIN
SELECT @pos = CHARINDEX(@delimiter,@input);
IF(@pos<=0)
select @pos = len(@input)
IF(@pos <> LEN(@input))
SELECT @string = SUBSTRING(@input, 1, @pos-1);
ELSE
SELECT @string = SUBSTRING(@input, 1, @pos);
INSERT INTO @result SELECT @string
SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)
END
RETURN
END
다음 기능을 사용할 수 있습니다.
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
다음은 함수로 사용할 수도 있고 동일한 로직을 프로시저에 넣을 수도 있는 예입니다. - [dbo]의 --SELECT *fn_SplitString;
CREATE FUNCTION [dbo].[fn_SplitString]
(@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',')
RETURNS @retTable TABLE
(
[value] VARCHAR(MAX) NULL
)AS
BEGIN
DECLARE
@vCSV VARCHAR (MAX) = @CSV,
@vDelimeter VARCHAR (100) = @Delimeter;
IF @vDelimeter = ';'
BEGIN
SET @vCSV = REPLACE(@vCSV, ';', '~!~#~');
SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~');
END;
SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&'), '<', '<'), '>', '>'), '''', '''), '"', '"');
DECLARE @xml XML;
SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>';
INSERT INTO @retTable
SELECT
x.i.value('.', 'varchar(max)') AS COLUMNNAME
FROM @xml.nodes('//i')AS x(i);
RETURN;
END;
/*
T-SQL 분할 문자열에 대한 응답
Andy Robinson과 AviG의 답변을 바탕으로 합니다.
향상된 기능 참조: SQL Server에 후행 공간을 포함하지 않는 LEN 함수
이 '파일'은 마크다운 파일과 SQL 파일 모두로 유효해야 합니다.
*/
CREATE FUNCTION dbo.splitstring ( --CREATE OR ALTER
@stringToSplit NVARCHAR(MAX)
) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX))
AS BEGIN
DECLARE @name NVARCHAR(MAX)
DECLARE @pos BIGINT
SET @stringToSplit = @stringToSplit + ',' -- this should allow entries that end with a `,` to have a blank value in that "column"
WHILE ((LEN(@stringToSplit+'_') > 1)) BEGIN -- `+'_'` gets around LEN trimming terminal spaces. See URL referenced above
SET @pos = COALESCE(NULLIF(CHARINDEX(',', @stringToSplit),0),LEN(@stringToSplit+'_')) -- COALESCE grabs first non-null value
SET @name = SUBSTRING(@stringToSplit, 1, @pos-1) --MAX size of string of type nvarchar is 4000
SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned."
INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added
-- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @stringToSplit + '''(' + CAST(LEN(@stringToSplit+'_')-1 as nvarchar) + ')'
END
RETURN
END
GO
/*
테스트 케이스: 상기의 「확장 기능」이라고 하는 URL을 참조해 주세요.
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,b')
Item | L
--- | ---
a | 1
| 0
b | 1
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,')
Item | L
--- | ---
a | 1
| 0
| 0
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, ')
Item | L
--- | ---
a | 1
| 0
| 1
SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, c ')
Item | L
--- | ---
a | 1
| 0
c | 3
*/
가장 쉬운 방법:
- SQL Server 2016 설치
- STRING_SPLIT https://msdn.microsoft.com/en-us/library/mt684588.aspx 사용
express 에디션에서도 동작합니다. : )
언급URL : https://stackoverflow.com/questions/10914576/t-sql-split-string
반응형
'programing' 카테고리의 다른 글
| SQL Server:sp_who2의 필터 출력 (0) | 2023.04.06 |
|---|---|
| 저장되지 않은 SQL 쿼리 스크립트 복구 (0) | 2023.04.06 |
| IF를 실행하려면 어떻게 해야 하나요?그리고 SQL SELECT에? (0) | 2023.04.06 |
| 저장 프로시저의 결과 집합에서 열 선택 (0) | 2023.04.06 |
| 2017년 이전에 SQL Server에서 문자열을 트리밍하려면 어떻게 해야 합니까? (0) | 2023.04.06 |