반응형
T-SQL 분할 문자열
쉼표로 분리해야하는 문자열이 포함 된 SQL Server 2008 R2 열이 있습니다. StackOverflow에서 많은 답변을 보았지만 R2에서는 아무것도 작동하지 않습니다. 스플릿 함수 예제에 대한 선택 권한이 있는지 확인했습니다. 어떤 도움이라도 대단히 감사합니다.
나는 당신을 위해 작동하기 전에이 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와 while 루프 대신 더 셋 기반 접근 방식을 고려한 사람이 있습니까? 이 함수는 질문을 위해 작성되었으며 SQL Server 2008과 쉼표를 구분 기호로 사용했습니다 . SQL Server 2016 이상 (및 호환성 수준 130 이상)에서 STRING_SPLIT()더 나은 옵션 입니다.
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( modelSQL Server 2017에서는 9,980, 사용자 데이터베이스에서는 훨씬 높음)로 제한하지 않으려면 다음과 같은 다른 방법을 사용하여 숫자를 도출 할 수 있습니다. 자신 의 숫자 테이블 만들기 . 시스템 테이블을 사용하거나 직접 테이블을 만들 수없는 경우 재귀 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
그러나 문자열이 100자를 초과하는 재귀 오류를 피하려면 외부 쿼리에 OPTION (MAXRECURSION 0)(또는 MAXRECURSION <longest possible string length if < 32768>) 를 추가 해야합니다. 이것이 좋은 대안 이 아니라면 의견에서 지적한 대로이 답변 을보십시오 .
(또한 분리 문자는 NCHAR(<=1228). 왜인지 연구해야합니다.)
분할 함수에 대한 자세한 내용, while 루프 및 재귀 CTE가 확장되지 않는 이유 (및 증명) 및 애플리케이션 계층에서 문자열을 분할하는 경우 더 나은 대안 :
- http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
- http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql
- https://sqlblog.org/2010/07/07/splitting-a-list-of-integers-another-roundup
마지막으로 SQL Server 2016 에서 대기가 끝났습니다. 분할 문자열 기능이 도입되었습니다.STRING_SPLIT
select * From STRING_SPLIT ('a,b', ',') cs
XML, Tally 테이블, while 루프 등과 같은 문자열을 분할하는 다른 모든 방법은이 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
어떤 종류의 반복 (반복)을 사용하는 문자열 분할의 모든 기능은 성능이 떨어집니다. 세트 기반 솔루션으로 교체해야합니다.
이 코드는 훌륭하게 실행됩니다.
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
금지 된 문자의 경우 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
최소 코드로 일반적인 경우에 빠른 임시 솔루션이 필요한 경우이 재귀 적 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
이것을 독립 실행 형 명령문으로 사용하거나 위의 CTE를 쿼리에 b추가하면 다른 식과 결과 테이블을 결합하여 추가 표현식에 사용할 수 있습니다.
편집하다 (Shnugo 작성)
카운터를 추가하면 목록과 함께 위치 인덱스가 표시됩니다.
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
누군가가 필요로하는 경우 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
+ 앤디 로빈슨의 기능을 약간 수정했습니다. 이제 반품 테이블에서 필요한 부품 만 선택할 수 있습니다.
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
다음은 위의 게시물을 간단히 수정 한 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 xyz
개인적으로 나는이 기능을 사용합니다 :
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
다음은 함수로 사용하거나 동일한 논리를 프로 시저에 넣을 수있는 예입니다. --SELECT * from [dbo] .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;
재귀 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;
/ *
대답 T-SQL 분할 문자열
에서 답변을 바탕으로 앤디 로빈슨 과 AviG는
기능 심판을 강화 : SQL 서버에 후행 공백을 포함하지 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
* /
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
@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에서 테스트)
편집 : 올바른 테스트 코드
가장 쉬운 방법 :
- SQL Server 2016 설치
- STRING_SPLIT https://msdn.microsoft.com/en-us/library/mt684588.aspx 사용
그것은 익스프레스 에디션에서도 작동합니다 :).
참고 URL : https://stackoverflow.com/questions/10914576/t-sql-split-string
반응형
'Programing' 카테고리의 다른 글
| curl : (1) 프로토콜 https가 libcurl에서 지원되지 않거나 비활성화되었습니다 (0) | 2020.07.19 |
|---|---|
| 리프 서명을 확인할 수 없습니다 (0) | 2020.07.19 |
| 투명한 UIWebView를 만드는 방법 (0) | 2020.07.19 |
| 안드로이드 키보드의 완료 버튼을 사용하여 버튼을 클릭하십시오. (0) | 2020.07.19 |
| EditText에서 복사 / 붙여 넣기를 비활성화하는 방법 (0) | 2020.07.19 |