T-SQL: 문자열 연결과 반대 - 문자열을 여러 레코드로 분할하는 방법
중복 가능성:
SQL에서 문자열 분할
SQL에서 문자열 연결과 관련된 몇 가지 질문을 본 적이 있습니다.혼수상태로 구분된 문자열을 데이터 행으로 분할하는 것과 반대되는 문제에 어떻게 접근할 수 있는지 궁금합니다.
테이블이 있다고 칩시다.
userTypedTags(userID,commaSeparatedTags) 'one entry per user
tags(tagID,name)
그리고 테이블에 데이터를 삽입하고 싶다.
userTag(userID,tagID) 'multiple entries per user
영감을 받아 데이터베이스에 없는 태그는 무엇입니까?질문.
편집
답해 주셔서 감사합니다.실제로 1개 이상의 것이 받아들여질 자격이 있지만, 저는 1개 밖에 선택할 수 없습니다.재귀가 있는 케이드 루가 제시한 해결책은 매우 깨끗한 것 같습니다.SQL Server 2005 이상에서 작동합니다.
이전 버전의 SQL Server에서는 miies에서 제공하는 솔루션을 사용할 수 있습니다.텍스트 데이터로 작업할 경우 wcm의 답변이 도움이 됩니다.다시한번 감사합니다.
이 문제에 대한 다양한 해결책이 여기에 문서화되어 있습니다.여기에는 다음과 같은 작은 보석이 포함되어 있습니다.
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
또한 XML을 사용하여 이 효과를 얻을 수 있습니다.이것에 의해, 모든 것에 재귀가 포함되어 있는 것처럼 보이는 응답의 제한이 없어집니다.여기서 사용한 특정 용도는 최대 32글자의 딜리미터를 사용할 수 있지만, 필요한 만큼 늘릴 수 있습니다.
create FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT r.value('.','VARCHAR(MAX)') as Item
FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(REPLACE(REPLACE(@s,'& ','& '),'<','<'), @sep, '</r><r>') + '</r></root>') as valxml) x
CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
)
그런 다음 다음을 사용하여 호출할 수 있습니다.
SELECT * FROM dbo.Split(' ', 'I hate bunnies')
반환되는 내용:
-----------
|I |
|---------|
|hate |
|---------|
|bunnies |
-----------
I should note, I don't actually hate bunnies... it just popped into my head for some reason.
The following is the closest thing I could come up with using the same method in an inline table-valued function. DON'T USE IT, IT'S HORRIBLY INEFFICIENT! It's just here for reference sake.
CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT r.value('.','VARCHAR(MAX)') as Item
FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>') as valxml) x
CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
)
이 기능(SQL Server 2005 이상)을 사용하고 있습니다.
create function [dbo].[Split]
(
@string nvarchar(4000),
@delimiter nvarchar(10)
)
returns @table table
(
[Value] nvarchar(4000)
)
begin
declare @nextString nvarchar(4000)
declare @pos int, @nextPos int
set @nextString = ''
set @string = @string + @delimiter
set @pos = charindex(@delimiter, @string)
set @nextPos = 1
while (@pos <> 0)
begin
set @nextString = substring(@string, 1, @pos - 1)
insert into @table
(
[Value]
)
values
(
@nextString
)
set @string = substring(@string, @pos + len(@delimiter), len(@string))
set @nextPos = @pos
set @pos = charindex(@delimiter, @string)
end
return
end
문자열을 단어로 분할하는 특별한 경우에 대해 SQL Server 2008을 위한 다른 솔루션을 찾았습니다.
with testTable AS
(
SELECT 1 AS Id, N'how now brown cow' AS txt UNION ALL
SELECT 2, N'she sells sea shells upon the sea shore' UNION ALL
SELECT 3, N'red lorry yellow lorry' UNION ALL
SELECT 4, N'the quick brown fox jumped over the lazy dog'
)
SELECT display_term, COUNT(*) As Cnt
FROM testTable
CROSS APPLY sys.dm_fts_parser('"' + txt + '"', 1033, 0,0)
GROUP BY display_term
HAVING COUNT(*) > 1
ORDER BY Cnt DESC
돌아온다
display_term Cnt
------------------------------ -----------
the 3
brown 2
lorry 2
sea 2
위 솔루션을 약간 수정하여 가변 길이 구분 기호로 작동합니다.
create FUNCTION dbo.fn_Split2 (@sep nvarchar(10), @s nvarchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
FROM Pieces
)
NB: 후행 공백이 있으면 len()이 잘못 보고되기 때문에 datalength()를 사용했습니다.
여기 있습니다.Split2005년 이전 버전의 SQL Server와 호환되는 기능입니다.
CREATE FUNCTION dbo.Split(@data nvarchar(4000), @delimiter nvarchar(100))
RETURNS @result table (Id int identity(1,1), Data nvarchar(4000))
AS
BEGIN
DECLARE @pos INT
DECLARE @start INT
DECLARE @len INT
DECLARE @end INT
SET @len = LEN('.' + @delimiter + '.') - 2
SET @end = LEN(@data) + 1
SET @start = 1
SET @pos = 0
WHILE (@pos < @end)
BEGIN
SET @pos = CHARINDEX(@delimiter, @data, @start)
IF (@pos = 0) SET @pos = @end
INSERT @result (data) SELECT SUBSTRING(@data, @start, @pos - @start)
SET @start = @pos + @len
END
RETURN
END
CLR을 사용하면 모든 경우에 사용할 수 있는 훨씬 더 간단한 대안을 제시합니다. 단, 일반적인 답변보다 40% 더 빠릅니다.
using System;
using System.Collections;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public class UDF
{
[SqlFunction(FillRowMethodName="FillRow")]
public static IEnumerable RegexSplit(SqlString s, SqlString delimiter)
{
return Regex.Split(s.Value, delimiter.Value);
}
public static void FillRow(object row, out SqlString str)
{
str = new SqlString((string) row);
}
}
물론 아직 Postgre보다 8배 느립니다.SQL의regexp_split_to_table.
SELECT substring(commaSeparatedTags,0,charindex(',',commaSeparatedTags))
첫 번째 태그가 표시됩니다.마찬가지로 서브스트링과 charindex를 1층씩 조합하여두 번째 레이어를 취득할 수도 있습니다.이는 즉각적인 해결책이지만 쿼리의 크기가 매우 빠르게 커지고 읽을 수 없게 되기 때문에 매우 적은 수의 태그에서만 작동합니다.그 후, 이 투고에 대한 보다 상세한 회답에 기재되어 있는 기능에 대해 설명합니다.
이거 옛날에 쓴 거예요.딜리미터는 쉼표이며 개별 값이 127자를 넘지 않는 것으로 가정합니다.그것은 꽤 쉽게 수정될 수 있다.
4,000자로 제한되지 않는다는 장점이 있습니다.
행운을 빕니다.
ALTER Function [dbo].[SplitStr] (
@txt text
)
Returns @tmp Table
(
value varchar(127)
)
as
BEGIN
declare @str varchar(8000)
, @Beg int
, @last int
, @size int
set @size=datalength(@txt)
set @Beg=1
set @str=substring(@txt,@Beg,8000)
IF len(@str)<8000 set @Beg=@size
ELSE BEGIN
set @last=charindex(',', reverse(@str))
set @str=substring(@txt,@Beg,8000-@last)
set @Beg=@Beg+8000-@last+1
END
declare @workingString varchar(25)
, @stringindex int
while @Beg<=@size Begin
WHILE LEN(@str) > 0 BEGIN
SELECT @StringIndex = CHARINDEX(',', @str)
SELECT
@workingString = CASE
WHEN @StringIndex > 0 THEN SUBSTRING(@str, 1, @StringIndex-1)
ELSE @str
END
INSERT INTO
@tmp(value)
VALUES
(cast(rtrim(ltrim(@workingString)) as varchar(127)))
SELECT @str = CASE
WHEN CHARINDEX(',', @str) > 0 THEN SUBSTRING(@str, @StringIndex+1, LEN(@str))
ELSE ''
END
END
set @str=substring(@txt,@Beg,8000)
if @Beg=@size set @Beg=@Beg+1
else IF len(@str)<8000 set @Beg=@size
ELSE BEGIN
set @last=charindex(',', reverse(@str))
set @str=substring(@txt,@Beg,8000-@last)
set @Beg=@Beg+8000-@last+1
END
END
return
END
"네이선 휠러"라는 답변을 상향 투표해 보니 "케이드 루"라는 답변이 특정 문자열 크기 이상에서는 작동하지 않았습니다.
몇 가지 포인트
DISTINT 키워드를 추가하면 퍼포먼스가 향상되었습니다.
- Nathan의 답변은 식별자가 5자 이하일 경우에만 유효합니다.물론 그것을 조정할 수 있습니다.분할하는 아이템이 나와 같은 INT 식별자라면 아래 나와 동일하게 취급할 수 있습니다.
CREATE FUNCTION [dbo].Split
(
@sep VARCHAR(32),
@s VARCHAR(MAX)
)
RETURNS
@result TABLE (
Id INT NULL
)
AS
BEGIN
DECLARE @xml XML
SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'
INSERT INTO @result(Id)
SELECT DISTINCT r.value('.','int') as Item
FROM @xml.nodes('//root//r') AS RECORDS(r)
RETURN
END
이 작업은 보통 다음 코드로 수행합니다.
create function [dbo].[Split](@string varchar(max), @separator varchar(10))
returns @splited table ( stringPart varchar(max) )
with execute as caller
as
begin
declare @stringPart varchar(max);
set @stringPart = '';
while charindex(@separator, @string) > 0
begin
set @stringPart = substring(@string, 0, charindex(@separator, @string));
insert into @splited (stringPart) values (@stringPart);
set @string = substring(@string, charindex(@separator, @string) + len(@separator), len(@string) + 1);
end
return;
end
go
다음 쿼리를 사용하여 테스트할 수 있습니다.
declare @example varchar(max);
set @example = 'one;string;to;rule;them;all;;';
select * from [dbo].[Split](@example, ';');
언급URL : https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco
'IT' 카테고리의 다른 글
| 이전 커밋을 체크한 후 최신 커밋으로 돌아가려면 어떻게 해야 합니까? (0) | 2023.04.13 |
|---|---|
| iOS 7의 기본 파란색을 프로그래밍 방식으로 가져오려면 어떻게 해야 합니다. (0) | 2023.04.13 |
| C# 프로그램을 50밀리초 동안 sleep 상태로 만들려면 어떻게 해야 하나요? (0) | 2023.04.08 |
| '<' 연산자는 향후 사용을 위해 예약되어 있습니다. (0) | 2023.04.08 |
| 특정 확장자를 가진 모든 파일을 검색하여 교체하는 PowerShell 스크립트 (0) | 2023.04.08 |