개별 항목에 액세스할 수 있도록 구분된 문자열을 분할하려면 어떻게 해야 합니까?
SQL Server를 사용하여 x 항목에 액세스할 수 있도록 문자열을 분할하려면 어떻게 해야 합니까?
"안녕, 존 스미스"라는 문자열을 사용합니다.공백으로 문자열을 분할하여 "John"을 반환해야 하는 인덱스 1의 항목에 액세스하려면 어떻게 해야 합니까?
SQL Server에는 분할 기능이 내장되어 있지 않기 때문에 UDF를 제외하고 내가 알고 있는 유일한 답은 PARSENAME 함수를 하이잭하는 것입니다.
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)
PARSENAME은 문자열을 마침표 문자로 분할합니다.두 번째 인수로 숫자를 사용하고, 이 숫자는 반환되는 문자열의 세그먼트(뒤에서 앞으로 작업)를 지정합니다.
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello
분명한 문제는 문자열에 마침표가 이미 포함되어 있는 경우입니다.그래도 UDF를 사용하는 게 가장 좋은 방법이라고 생각해다른 조언은 없나요?
구분된 문자열을 구문 분석하는 SQL 사용자 정의 함수의 솔루션이 도움이 될 수 있습니다(코드 프로젝트).
다음과 같은 간단한 논리를 사용할 수 있습니다.
Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null
WHILE LEN(@products) > 0
BEGIN
IF PATINDEX('%|%', @products) > 0
BEGIN
SET @individual = SUBSTRING(@products,
0,
PATINDEX('%|%', @products))
SELECT @individual
SET @products = SUBSTRING(@products,
LEN(@individual + '|') + 1,
LEN(@products))
END
ELSE
BEGIN
SET @individual = @products
SET @products = NULL
SELECT @individual
END
END
먼저 함수를 만듭니다(CTE를 사용하면 일반적인 테이블 표현으로 임시 테이블이 필요하지 않습니다).
create function dbo.SplitString
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@str,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)
GO
그런 다음 이렇게 테이블로 사용합니다(또는 기존 저장된 proc에 맞게 수정).
select s
from dbo.SplitString('Hello John Smith', ' ')
where zeroBasedOccurance=1
갱신하다
입력 문자열이 4000자를 초과하면 이전 버전이 실패합니다.이 버전에서는 다음과 같은 제한이 해결됩니다.
create function dbo.SplitString
(
@str nvarchar(max),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ItemIndex,
substring(
@str,
a,
case when b > 0 then b-a ELSE LEN(@str) end)
AS s
from tokens
);
GO
용도는 그대로입니다.
이 솔루션의 대부분은 루프 또는 재귀 CTE를 사용하는 동안 사용합니다.공백 이외의 딜리미터를 사용할 수 있는 경우, 세트 베이스의 어프로치가 우수합니다.
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM
(
SELECT n = Number,
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) AS y
);
사용 예:
SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
WHERE idx = 3;
결과:
----
blat
하다, 하다, 하다, 하다, 하다, 이런 것도 쓸 수 요.idx당신은 함수에 대한 논쟁으로 원하지만, 그건 독자들에게 연습으로 남겨둘게요.
SQL Server 2016에 추가된 네이티브 함수만으로는 출력이 원래 목록 순서대로 렌더링된다는 보장이 없기 때문에 이 작업을 수행할 수 없습니다.바꿔 말하면, 만약 당신이 통과한다면3,6,1결과는 그 순서로 나올 것 같지만1,3,6여기에 내장된 기능을 개선하기 위해 커뮤니티에 도움을 요청했습니다.
질적 피드백이 충분하면 실제로 다음과 같은 개선을 고려할 수 있습니다.
분할 함수에 대한 자세한 내용, 루프 및 재귀 CTE가 확장되지 않는 이유(및 증명) 및 애플리케이션 계층에서 나오는 문자열을 분할하는 경우 더 나은 대안:
- 올바른 방법으로 또는 차선책으로 문자열 분할
- 문자열 분할: 폴로업
- 문자열 분할 : T-SQL 감소
- 문자열 분할/연결 방식 비교
- 정수 목록 처리 : my approach
- 정수 목록 분할: 다른 반올림
- 목록 분할에 대한 자세한 내용: 사용자 지정 구분 기호, 중복 방지 및 순서 유지
- SQL Server 문자열에서 중복 제거
단, SQL Server 2016 이상에서는및 을 참조해 주세요.
- 퍼포먼스의 이상과 전제 조건: STRING_SPLIT()
- SQL Server 2016의 STRING_SPLIT() : 후속 조치 #1
- SQL Server 2016의 STRING_SPLIT() : 후속 조치 #2
- SQL Server v.다음 : STRING_AGG() 퍼포먼스
- SQL Server의 새로운 STRING_AGG 및 STRING_SPLIT 함수로 오래된 문제 해결
숫자 테이블을 사용하여 문자열 해석을 수행할 수 있습니다.
물리 번호 테이블을 만듭니다.
create table dbo.Numbers (N int primary key);
insert into dbo.Numbers
select top 1000 row_number() over(order by number) from master..spt_values
go
1000000 행의 테스트 테이블 작성
create table #yak (i int identity(1,1) primary key, array varchar(50))
insert into #yak(array)
select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
go
함수를 만듭니다.
create function [dbo].[ufn_ParseArray]
( @Input nvarchar(4000),
@Delimiter char(1) = ',',
@BaseIdent int
)
returns table as
return
( select row_number() over (order by n asc) + (@BaseIdent - 1) [i],
substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
from dbo.Numbers
where n <= convert(int, len(@Input)) and
substring(@Delimiter + @Input, n, 1) = @Delimiter
)
go
사용방법(노트북에서는 40대당 300만 행 출력)
select *
from #yak
cross apply dbo.ufn_ParseArray(array, ',', 1)
정리하다
drop table dbo.Numbers;
drop function [dbo].[ufn_ParseArray]
여기서의 퍼포먼스는 대단하지는 않지만, 100만 이상의 함수를 호출하는 것은 좋은 생각이 아닙니다.문자열을 여러 행으로 분할할 경우 이 기능은 사용하지 않습니다.
이 질문은 문자열 분할 방식이 아니라 n번째 요소를 얻는 방법에 대한 것입니다.
의 모든 재귀를 하여 문자열 .CTE 여러 s, "s"CHARINDEX,REVERSE ★★★★★★★★★★★★★★★★★」PATINDEX 개발,, 테이블, CLR 메서드 호출,CROSS APPLY행을 합니다. 대부분의 답변은 여러 줄의 코드를 포함합니다.
하지만, 정말로 n번째 요소를 얻기 위한 접근법만을 원한다면, 이것은 실제 원라이너로 할 수 있고, UDF도 없고, 서브 셀렉트도 할 수 없습니다.기타 이점: safe를 입력합니다.
공백으로 구분된 파트 2를 가져옵니다.
DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')
물론 딜리미터와 위치에 변수를 사용할 수 있습니다(사용).sql:column쿼리 값에서 직접 위치를 검색하려면:
DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')
문자열에 금지된 문자(특히 다음 중 하나)가 포함될 수 있는 경우&><이 방법으로도 할 수 있습니다. 쓰세요.FOR XML PATH모든 금지된 문자를 암묵적으로 적절한 이스케이프 시퀀스로 대체하려면 먼저 문자열에 입력합니다.
딜리미터가 세미콜론인 경우는 매우 특별한 경우입니다.이 경우 딜리미터를 먼저 '#DLMT#'로 바꾸고 마지막으로 XML 태그로 바꿉니다.
SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');
SQL-Server 2016+ 업데이트
하지 않았습니다.STRING_SPLIT 2016하면 SQL-Server 2016+가 있습니다JSON_VALUE ★★★★★★★★★★★★★★★★★」OPENJSON.
★★★★★★★★★★★★★★★★ JSON_VALUE인덱스 배열의 위치를 통과할 수 있습니다.
★★★의 OPENJSON문서에는 다음과 같이 명기되어 있습니다.
OPENJSON이 JSON 배열을 해석하면 함수는 JSON 텍스트에 포함된 요소의 인덱스를 키로 반환합니다.
「 」와 같은 1,2,3괄호만.[1,2,3].
a a a like like like like like like like like like like like like와 같은 일련의 .this is an exampleneeds ["this","is","an","example"].
이것들은 매우 간단한 문자열 조작입니다.시험해 보세요.
DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;
--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));
--위치 안전 문자열 스플리터(제로 베이스)에 대해서는, 다음을 참조해 주세요.
SELECT JsonArray.[key] AS [Position]
,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray
이 투고에서는, 다양한 어프로치를 테스트해, 다음과 같이 하고 있습니다.OPENJSON한 "delimited 메서드보다 .구분된 분할 8k()입니다.
업데이트 2 - type-safe 값을 가져옵니다.
어레이 내의 어레이를 사용할 때는 double을 사용하는 것만으로 사용 가능[[]] 하면 이 합니다.WITHfilenovel:
DECLARE @SomeDelimitedString VARCHAR(100)='part1|1|20190920';
DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');
SELECT @SomeDelimitedString AS TheOriginal
,@JsonArray AS TransformedToJSON
,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
,TheSecondFragment INT '$[1]'
,TheThirdFragment DATE '$[2]') ValuesFromTheArray
여기 그것을 할 UDF가 있습니다.구분된 값의 테이블이 반환되며, 이 테이블에서 모든 시나리오를 시도한 것은 아니지만 예제는 정상적으로 작동합니다.
CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)
--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0
Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
Insert Into @ReturnTable(part)
Select @part
Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))
Select @iSpaces = charindex(@deliminator,@myString,0)
end
If len(@myString) > 0
Insert Into @ReturnTable
Select @myString
RETURN
END
GO
이렇게 부르죠.
Select * From SplitString('Hello John Smith',' ')
편집: 에서와 같이 len>1의 딜리미터를 처리하도록 솔루션이 갱신되었습니다.
select * From SplitString('Hello**John**Smith','**')
여기 간단한 해결 방법을 게시합니다.
CREATE FUNCTION [dbo].[split](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
을 실행해보세요.
select * from dbo.split('Hello John Smith',' ')
내 생각엔 너희들이 너무 복잡하게 만들고 있는 것 같아.CLR UDF를 만들고 완료하기만 하면 됩니다.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
public partial class UserDefinedFunctions {
[SqlFunction]
public static SqlString SearchString(string Search) {
List<string> SearchWords = new List<string>();
foreach (string s in Search.Split(new char[] { ' ' })) {
if (!s.ToLower().Equals("or") && !s.ToLower().Equals("and")) {
SearchWords.Add(s);
}
}
return new SqlString(string.Join(" OR ", SearchWords.ToArray()));
}
};
'어디서'를 쓰면 요?string ★★★★★★★★★★★★★★★★★」values()★★★★★★★★★★★★?
DECLARE @str varchar(max)
SET @str = 'Hello John Smith'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, '''),(''')
SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)'
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
결과 세트를 달성했습니다.
id item
1 Hello
2 John
3 Smith
Frederic의 답변을 사용하지만 SQL Server 2005에서는 작동하지 않았습니다.
selectunion all가 있다
DECLARE @str varchar(max)
SET @str = 'Hello John Smith how are you'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT ''' + @str + ''' '
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
결과 집합은 다음과 같습니다.
id item
1 Hello
2 John
3 Smith
4 how
5 are
6 you
이 패턴은 잘 작동하며 일반화할 수 있습니다.
Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
^^^^^ ^^^^^ ^^^^
참고 필드, 색인 및 유형.
다음과 같은 식별자가 있는 테이블을 사용합니다.
sys.message.1234.warning.A45
sys.message.1235.error.O98
....
그러면 글을 쓸 수 있어요.
SELECT Source = q.value('(/n[1])', 'varchar(10)'),
RecordType = q.value('(/n[2])', 'varchar(20)'),
RecordNumber = q.value('(/n[3])', 'int'),
Status = q.value('(/n[4])', 'varchar(5)')
FROM (
SELECT q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
FROM some_TABLE
) Q
모든 부품을 쪼개서 주조합니다.
또 다른 것은 딜리미터 함수로 문자열의 n번째 부분을 구합니다.
create function GetStringPartByDelimeter (
@value as nvarchar(max),
@delimeter as nvarchar(max),
@position as int
) returns NVARCHAR(MAX)
AS BEGIN
declare @startPos as int
declare @endPos as int
set @endPos = -1
while (@position > 0 and @endPos != 0) begin
set @startPos = @endPos + 1
set @endPos = charindex(@delimeter, @value, @startPos)
if(@position = 1) begin
if(@endPos = 0)
set @endPos = len(@value) + 1
return substring(@value, @startPos, @endPos - @startPos)
end
set @position = @position - 1
end
return null
end
사용방법:
select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)
반환되는 값:
c
데이터베이스의 호환성 수준이 130 이상인 경우 STRING_SPLIT 함수를 OFFSET FETCH 절과 함께 사용하여 인덱스별로 특정 항목을 가져올 수 있습니다.
인덱스 N(제로 기준)에서 항목을 가져오려면 다음 코드를 사용할 수 있습니다.
SELECT value
FROM STRING_SPLIT('Hello John Smith',' ')
ORDER BY (SELECT NULL)
OFFSET N ROWS
FETCH NEXT 1 ROWS ONLY
데이터베이스의 호환성 수준을 확인하려면 다음 코드를 실행합니다.
SELECT compatibility_level
FROM sys.databases WHERE name = 'YourDBName';
이것을 시험해 보세요.
CREATE function [SplitWordList]
(
@list varchar(8000)
)
returns @t table
(
Word varchar(50) not null,
Position int identity(1,1) not null
)
as begin
declare
@pos int,
@lpos int,
@item varchar(100),
@ignore varchar(100),
@dl int,
@a1 int,
@a2 int,
@z1 int,
@z2 int,
@n1 int,
@n2 int,
@c varchar(1),
@a smallint
select
@a1 = ascii('a'),
@a2 = ascii('A'),
@z1 = ascii('z'),
@z2 = ascii('Z'),
@n1 = ascii('0'),
@n2 = ascii('9')
set @ignore = '''"'
set @pos = 1
set @dl = datalength(@list)
set @lpos = 1
set @item = ''
while (@pos <= @dl) begin
set @c = substring(@list, @pos, 1)
if (@ignore not like '%' + @c + '%') begin
set @a = ascii(@c)
if ((@a >= @a1) and (@a <= @z1))
or ((@a >= @a2) and (@a <= @z2))
or ((@a >= @n1) and (@a <= @n2))
begin
set @item = @item + @c
end else if (@item > '') begin
insert into @t values (@item)
set @item = ''
end
end
set @pos = @pos + 1
end
if (@item > '') begin
insert into @t values (@item)
end
return
end
다음과 같이 테스트합니다.
select * from SplitWordList('Hello John Smith')
인터넷에서 솔루션을 찾고 있었는데, 아래와 같이 해결했습니다.레퍼런스
이 함수를 다음과 같이 부릅니다.
SELECT * FROM dbo.split('ram shyam hari gopal',' ')
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SELECT @idx = 1
IF len(@String)<1 OR @String IS NULL RETURN
WHILE @idx!= 0
BEGIN
SET @idx = charindex(@Delimiter,@String)
IF @idx!=0
SET @slice = LEFT(@String,@idx - 1)
ELSE
SET @slice = @String
IF(len(@slice)>0)
INSERT INTO @temptable(Items) VALUES(@slice)
SET @String = RIGHT(@String,len(@String) - @idx)
IF len(@String) = 0 break
END
RETURN
END
다음 예제에서는 재귀 CTE를 사용하고 있습니다.
업데이트 18.09.2013
CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
(
SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter, @List + @Delimiter)) AS val,
CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval,
1 AS [level]
UNION ALL
SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
[level] + 1
FROM cte
WHERE stval != ''
)
INSERT @returns
SELECT REPLACE(val, ' ','' ) AS val, [level]
FROM cte
WHERE val > ''
RETURN
END
하드웨어 데모
Alter Function dbo.fn_Split
(
@Expression nvarchar(max),
@Delimiter nvarchar(20) = ',',
@Qualifier char(1) = Null
)
RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))
AS
BEGIN
/* USAGE
Select * From dbo.fn_Split('apple pear grape banana orange honeydew cantalope 3 2 1 4', ' ', Null)
Select * From dbo.fn_Split('1,abc,"Doe, John",4', ',', '"')
Select * From dbo.fn_Split('Hello 0,"&""&&&&', ',', '"')
*/
-- Declare Variables
DECLARE
@X xml,
@Temp nvarchar(max),
@Temp2 nvarchar(max),
@Start int,
@End int
-- HTML Encode @Expression
Select @Expression = (Select @Expression For XML Path(''))
-- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||
While PATINDEX('%' + @Qualifier + '%', @Expression) > 0 AND Len(IsNull(@Qualifier, '')) > 0
BEGIN
Select
-- Starting character position of @Qualifier
@Start = PATINDEX('%' + @Qualifier + '%', @Expression),
-- @Expression starting at the @Start position
@Temp = SubString(@Expression, @Start + 1, LEN(@Expression)-@Start+1),
-- Next position of @Qualifier within @Expression
@End = PATINDEX('%' + @Qualifier + '%', @Temp) - 1,
-- The part of Expression found between the @Qualifiers
@Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End,
-- New @Expression
@Expression = REPLACE(@Expression,
@Qualifier + @Temp2 + Case When @End < 0 Then '' Else @Qualifier End,
Replace(@Temp2, @Delimiter, '|||***|||')
)
END
-- Replace all occurences of @Delimiter within @Expression with '</fn_Split><fn_Split>'
-- And convert it to XML so we can select from it
SET
@X = Cast('<fn_Split>' +
Replace(@Expression, @Delimiter, '</fn_Split><fn_Split>') +
'</fn_Split>' as xml)
-- Insert into our returnable table replacing '|||***|||' back to @Delimiter
INSERT @Results
SELECT
"Value" = LTRIM(RTrim(Replace(C.value('.', 'nvarchar(max)'), '|||***|||', @Delimiter)))
FROM
@X.nodes('fn_Split') as X(C)
-- Return our temp table
RETURN
END
함수를 사용하지 않고 SQL에서 문자열을 분할할 수 있습니다.
DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'
-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT
x.XmlCol.value('.', 'varchar(36)') AS val
FROM
(
SELECT
CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);
임의의 문자열(xml 특수문자 포함)을 지원해야 하는 경우
DECLARE @bla NVARCHAR(MAX)
SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'
-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT
x.XmlCol.value('.', 'nvarchar(MAX)') AS val
FROM
(
SELECT
CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);
Database(으로 하지만 는 않음에서는 Azure SQL의 "Microsoft SQL Server"가 됩니다.STRING_SPLIT을 사용하다
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
enable_ordinal에는 '1'이라는 됩니다.ordinal입력 문자열 내의 서브스트링의 1 기반 위치로 구성됩니다.
SELECT *
FROM STRING_SPLIT('hello john smith', ' ', 1)
| value | ordinal |
|-------|---------|
| hello | 1 |
| john | 2 |
| smith | 3 |
이를 통해 다음과 같은 작업을 수행할 수 있습니다.
SELECT value
FROM STRING_SPLIT('hello john smith', ' ', 1)
WHERE ordinal = 2
| value |
|-------|
| john |
ifenable_ordinal를 사용할 수 없는 경우 입력 문자열 내의 서브스트링이 고유하다고 가정하는 트릭이 있습니다.이 시나리오에서는CHAR_INDEX하기 위해서 사용할 수 .
SELECT value, ROW_NUMBER() OVER (ORDER BY CHARINDEX(value, input_str)) AS ord_pos
FROM (VALUES
('hello john smith')
) AS x(input_str)
CROSS APPLY STRING_SPLIT(input_str, ' ')
| value | ord_pos |
|-------+---------|
| hello | 1 |
| john | 2 |
| smith | 3 |
오래된 질문인 것은 알지만, 제 솔루션에서 혜택을 볼 수 있는 질문도 있을 것 같습니다.
select
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,1
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
,LEN(column_name))
from table_name
장점:
- 3개의 서브스트링 모두 디미네이터를 ' '로 구분합니다.
- while loop을 사용하면 성능이 저하되므로 사용하지 마십시오.
- 모든 결과 하위 문자열이 한 행에 표시되므로 피벗할 필요가 없습니다.
제한 사항:
- 스페이스(서브스트링)의 합계수를 알 필요가 있습니다.
주의: 이 솔루션은 최대 N개의 서브스트링을 제공할 수 있습니다.
한계를 극복하기 위해 다음 참조를 사용할 수 있습니다.
그러나 위의 솔루션은 테이블에서 사용할 수 없습니다(실제로 사용할 수 없었습니다).
다시 한번 이 해결책이 누군가에게 도움이 되기를 바란다.
업데이트: 레코드 >50000의 경우 퍼포먼스가 저하되므로 사용하지 않는 것이 좋습니다.
를--- using using using using using using using using using using using 를 사용한 순수한 세트 기반 솔루션TVF재귀적 '재귀적'으로CTE 하면 됩니다.JOIN ★★★★★★★★★★★★★★★★★」APPLY이 기능을 모든 데이터 세트에 적용합니다.
create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
union all
select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
, left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
, [no] + 1 [no]
from r where value > '')
select ltrim(x) [value], [no] [index] from r where x is not null;
go
사용방법:
select *
from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')
where [index] = 1;
결과:
value index
-------------
John 1
다른 응답은 거의 모두 분할된 문자열을 대체하고 있으며 이로 인해 CPU 사이클이 낭비되고 불필요한 메모리 할당이 수행됩니다.
여기서 문자열 분할을 하는 훨씬 더 좋은 방법을 소개합니다.http://www.digitalruby.com/split-string-sql-server/
코드는 다음과 같습니다.
SET NOCOUNT ON
-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
WHILE @SplitEndPos > 0
BEGIN
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
INSERT @SplitStringTable (Value) VALUES (@SplitValue)
SET @SplitStartPos = @SplitEndPos + 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)
SET NOCOUNT OFF
-- You can select or join with the values in @SplitStringTable at this point.
서버에 문제가 있는 재귀적 CTE 솔루션, 테스트
MS SQL Server 2008 스키마 셋업:
create table Course( Courses varchar(100) );
insert into Course values ('Hello John Smith');
질문 1:
with cte as
( select
left( Courses, charindex( ' ' , Courses) ) as a_l,
cast( substring( Courses,
charindex( ' ' , Courses) + 1 ,
len(Courses ) ) + ' '
as varchar(100) ) as a_r,
Courses as a,
0 as n
from Course t
union all
select
left(a_r, charindex( ' ' , a_r) ) as a_l,
substring( a_r, charindex( ' ' , a_r) + 1 , len(a_R ) ) as a_r,
cte.a,
cte.n + 1 as n
from Course t inner join cte
on t.Courses = cte.a and len( a_r ) > 0
)
select a_l, n from cte
--where N = 1
결과:
| A_L | N |
|--------|---|
| Hello | 0 |
| John | 1 |
| Smith | 2 |
josejuan의 xml 기반 답변과 비슷하지만 xml 경로를 한 번만 처리하면 피벗이 적당히 효율적이라는 것을 알게 되었습니다.
select ID,
[3] as PathProvidingID,
[4] as PathProvider,
[5] as ComponentProvidingID,
[6] as ComponentProviding,
[7] as InputRecievingID,
[8] as InputRecieving,
[9] as RowsPassed,
[10] as InputRecieving2
from
(
select id,message,d.* from sysssislog cross apply (
SELECT Item = y.i.value('(./text())[1]', 'varchar(200)'),
row_number() over(order by y.i) as rn
FROM
(
SELECT x = CONVERT(XML, '<i>' + REPLACE(Message, ':', '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
) d
WHERE event
=
'OnPipelineRowsSent'
) as tokens
pivot
( max(item) for [rn] in ([3],[4],[5],[6],[7],[8],[9],[10])
) as data
8시 30분에 실행.
select id,
tokens.value('(/n[3])', 'varchar(100)')as PathProvidingID,
tokens.value('(/n[4])', 'varchar(100)') as PathProvider,
tokens.value('(/n[5])', 'varchar(100)') as ComponentProvidingID,
tokens.value('(/n[6])', 'varchar(100)') as ComponentProviding,
tokens.value('(/n[7])', 'varchar(100)') as InputRecievingID,
tokens.value('(/n[8])', 'varchar(100)') as InputRecieving,
tokens.value('(/n[9])', 'varchar(100)') as RowsPassed
from
(
select id, Convert(xml,'<n>'+Replace(message,'.','</n><n>')+'</n>') tokens
from sysssislog
WHERE event
=
'OnPipelineRowsSent'
) as data
9시 20분에 실행
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
사용방법
select *from dbo.fnSplitString('Querying SQL Server','')
구분된 텍스트의 일부만 가져오기를 원하는 경우 이 기능을 사용할 수 있습니다.
SplitStringSep('Word1 wordr2 word3'', ')에서 *를 선택합니다.
CREATE function [dbo].[SplitStringSep]
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@str,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)
내가 이걸 옮겼는데
declare @x nvarchar(Max) = 'ali.veli.deli.';
declare @item nvarchar(Max);
declare @splitter char='.';
while CHARINDEX(@splitter,@x) != 0
begin
set @item = LEFT(@x,CHARINDEX(@splitter,@x))
set @x = RIGHT(@x,len(@x)-len(@item) )
select @item as item, @x as x;
end
주의할 것은 점 '.'뿐입니다.@x의 끝은 항상 거기에 있어야 합니다.
@Nothings Impossible 솔루션, 아니 가장 많이 투표된 답변(인정된 답변 바로 아래)에 대한 코멘트를 통해 다음과 같은 빠르고 더러운 솔루션이 자신의 요구를 충족한다는 것을 알게 되었습니다.이 솔루션은 SQL 도메인 내에서만 사용할 수 있다는 이점이 있습니다.
"첫 번째; 두 번째; 세 번째; 네 번째; 다섯 번째"라는 문자열이 주어진 경우, 예를 들어 세 번째 토큰을 얻고 싶습니다.이것은 문자열이 몇 개의 토큰을 가지고 있는지 알고 있는 경우에만 동작합니다.이 경우는 5입니다.따라서 마지막 2개의 토큰을 잘라내고(마지막 2개의 토큰을 잘라내고(마지막 2개의 토큰을 잘라내고) 첫 번째 2개의 토큰을 잘라냅니다(마지막 2개의 토큰을 잘라냅니다).
저는 이것이 추악하고 제가 처한 상황을 커버하고 있다는 것을 알지만, 누군가 유용하다고 생각할 때를 대비해서 글을 올립니다.건배.
select
REVERSE(
SUBSTRING(
reverse_substring,
0,
CHARINDEX(';', reverse_substring)
)
)
from
(
select
msg,
SUBSTRING(
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg)
)+1
)+1,
1000
) reverse_substring
from
(
select 'first;second;third;fourth;fifth' msg
) a
) b
declare @strng varchar(max)='hello john smith'
select (
substring(
@strng,
charindex(' ', @strng) + 1,
(
(charindex(' ', @strng, charindex(' ', @strng) + 1))
- charindex(' ',@strng)
)
))
언급URL : https://stackoverflow.com/questions/2647/how-do-i-split-a-delimited-string-so-i-can-access-individual-items
'programing' 카테고리의 다른 글
| SQL Server의 for-loop 구문 (0) | 2023.04.07 |
|---|---|
| TSQL 피벗(애그리게이트 기능 없음) (0) | 2023.04.07 |
| WHERE 절(SQL Server 사용 중)에서 대소문자를 구분하는 검색 방법은 무엇입니까? (0) | 2023.04.07 |
| 기존 테이블에서 열을 제거하려면 어떻게 해야 합니까? (0) | 2023.04.07 |
| SQL Server의 add column 문에 명명된 기본 제약 조건을 만들 수 있습니까? (0) | 2023.04.07 |