반응형
항목 x에 액세스 할 수 있도록 문자열을 어떻게 분할합니까?
SQL Server를 사용하여 항목 x에 액세스 할 수 있도록 문자열을 어떻게 분할합니까?
"Hello John Smith"문자열을 가져옵니다. 문자열을 공백으로 분할하고 인덱스 1에서 "John"을 반환해야하는 항목에 어떻게 액세스 할 수 있습니까?
구분 된 문자열을 구문 분석하는 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
SQL Server에 내장 분할 기능이 있다고 생각하지 않으므로 UDF 이외의 다른 대답은 PARSENAME 함수를 가로 채는 것입니다.
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)
PARSENAME은 문자열을 가져 와서 마침표 문자로 분할합니다. 두 번째 인수로 숫자를 취하며 그 숫자는 반환 할 문자열 세그먼트를 지정합니다 (뒤에서 앞으로 작동).
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello
명백한 문제는 문자열에 이미 마침표가 포함되어있을 때입니다. 여전히 UDF를 사용하는 것이 가장 좋은 방법이라고 생각합니다. 다른 제안이 있습니까?
먼저 함수를 작성하십시오 (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
그런 다음이 테이블을 테이블로 사용하거나 기존 스토어드 프로 시저에 맞게 수정하십시오.
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
사용법은 동일하게 유지됩니다.
여기에서 대부분의 솔루션은 while 루프 또는 재귀 CTE를 사용합니다. 세트 기반 접근 방식이 우수 할 것이라고 약속합니다.
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(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_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) AS y
);
분할 함수에 대한 자세한 내용, while 루프 및 재귀 CTE가 확장되지 않는 이유 (및 증명) 및 애플리케이션 계층에서 문자열을 분할하는 경우 더 나은 대안 :
- 현을 올바른 방법으로 또는 다음 최선의 방법으로 분리
- 문자열 분리 : 후속 작업
- 문자열 분할 : 이제 적은 T-SQL
- 문자열 분할 / 연결 방법 비교
- 정수 목록 처리 : 내 접근 방식
- 정수 목록 나누기 : 다른 반올림
- 분할 목록에 대한 추가 정보 : 사용자 지정 구분 기호, 중복 방지 및 순서 유지
- SQL Server에서 문자열에서 중복 제거
위의 SQL 서버 2016에,하지만 당신은 보라 STRING_SPLIT()와 STRING_AGG():
- 성능 놀라움과 가정 : STRING_SPLIT ()
- SQL Server 2016의 STRING_SPLIT () : 후속 작업 # 1
- SQL Server 2016의 STRING_SPLIT () : 후속 작업 # 2
- SQL Server v.Next : 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 대 3mil 행 출력)
select *
from #yak
cross apply dbo.ufn_ParseArray(array, ',', 1)
대청소
drop table dbo.Numbers;
drop function [dbo].[ufn_ParseArray]
여기서 성능은 놀랍지 않지만 백만 행 테이블 이상의 함수를 호출하는 것이 가장 좋은 아이디어는 아닙니다. 문자열을 여러 행으로 나누면 함수를 피할 수 있습니다.
이 질문은 문자열 분할 방식 이 아니라 n 번째 요소를 얻는 방법에 관한 것 입니다.
여기에 모든 답이 재귀를 사용하여 문자열을 분할 어떤 종류의 일을하는, CTE이야, 배수 CHARINDEX, REVERSE그리고 PATINDEX, 발명 기능은 CLR 방법, 번호 테이블에 대한 호출 CROSS APPLY로 ... 대부분의 답변은 많은 코드를 다룹니다.
그러나 -n 번째 요소를 얻는 접근법 이상 을 정말로 원한다면 -이것은 실제로 하나의 라이너로 , UDF는없고, 하위 선택조차도 할 수 없으며 ... 추가 혜택 : type 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. 그러나 SQL-Server 2016+를 사용하면 JSON_VALUE및이 OPENJSON있습니다.
함께 JSON_VALUE우리는 인덱스 '배열과 위치에 전달할 수있다.
설명서의 상태 OPENJSON는 다음 과 같이 명확하게 나타납니다.
OPENJSON이 JSON 배열을 구문 분석하면이 함수는 JSON 텍스트의 요소 색인을 키로 리턴합니다.
같은 문자열은 1,2,3대괄호 만 필요합니다 : [1,2,3].
같은 단어의 문자열은 this is an example이어야 ["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,']'));
-위치 안전 스트링 스플리터 ( 0부터 시작 )는 다음을 참조하십시오.
SELECT JsonArray.[key] AS [Position]
,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray
에서 이 게시물에 나는 다양한 접근 방법을 테스트하고, 발견 OPENJSON정말 빠릅니다. 유명한 "delimitedSplit8k ()"메소드보다 훨씬 빠릅니다.
업데이트 2-유형 안전 값 가져 오기
doubled를 사용하여 배열 내에서 배열을 사용할 수 있습니다 [[]]. 이것은 유형이 지정된 WITH-clause를 허용합니다 .
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로 delimter를 처리하도록 솔루션이 업데이트되었습니다.
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
프레드릭의 대답을 사용하지만 SQL Server 2005에서는 작동하지 않았습니다.
나는 그것을 수정하고 select함께 사용 union 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')
^^^^^ ^^^^^ ^^^^
FIELD , 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
모든 부품 분리 및 주조.
데이터베이스의 호환성 수준이 130 이상인 경우 STRING_SPLIT 함수를 OFFSET FETCH 절 과 함께 사용 하여 특정 항목을 인덱스별로 가져올 수 있습니다.
인덱스 N (0부터 시작)으로 항목을 가져 오려면 다음 코드를 사용할 수 있습니다.
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';
나는 그물에 대한 해결책을 찾고 있었고 아래는 나를 위해 작동합니다. 참조 .
그리고 당신은 다음과 같이 함수를 호출합니다 :
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
또 다른 함수는 delimeter 함수로 문자열의 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
이 시도:
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')
다음 예는 재귀 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
SQLFiddle 데모
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
나는 그것이 오래된 질문이라는 것을 알고 있지만, 어떤 사람은 내 솔루션에서 이익을 얻을 수 있다고 생각합니다.
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 개의 하위 문자열 deliminator를 모두 ''로 구분합니다.
- 성능을 저하 시키므로 while 루프를 사용하면 안됩니다.
- 모든 결과 하위 문자열이 하나의 행에 표시되므로 피벗 할 필요가 없습니다.
한계 :
- 하나는 전체 번호를 알아야합니다. 공백 (하위 문자열).
참고 : 솔루션은 하위 문자열을 최대 N까지 줄 수 있습니다.
한계를 극복하기 위해 다음과 같은 참조를 사용할 수 있습니다 .
그러나 다시 위의 솔루션 을 테이블에서 사용할 수 없습니다 (Actaully 나는 그것을 사용할 수 없었습니다).
다시 말하지만이 솔루션이 누군가를 도울 수 있기를 바랍니다.
업데이트 : 레코드가 50000보다 큰 경우 성능 이 저하되므로 사용 하지 않는 것이 좋습니다.LOOPS
거의 모든 다른 답변 분할 코드는 분할되는 문자열을 대체하여 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.
함수 없이도 문자열을 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);
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
시작 SQL 서버 2016 우리의 string_split
DECLARE @string varchar(100) = 'Richard, Mike, Mark'
SELECT value FROM string_split(@string, ',')
STRING_SPLIT를 사용하는 최신 접근 방식 에는 SQL Server 2016 이상이 필요합니다.
DECLARE @string varchar(100) = 'Hello John Smith'
SELECT
ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
value
FROM string_split(@string, ' ')
결과:
RowNr value
1 Hello
2 John
3 Smith
이제 행 번호에서 n 번째 요소를 얻을 수 있습니다.
Aaron Bertrand의 답변은 훌륭하지만 결함이 있습니다. length 함수는 후행 공백을 제거하기 때문에 (원래 질문의 예와 같이) 구분 기호로 공백을 정확하게 처리하지 않습니다.
다음은 공백 구분 기호를 허용하도록 약간 조정 된 코드입니다.
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(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_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim
) AS y
);
다음은 문자열을 나누고 항목 X에 액세스하는 질문의 목표를 달성하는 함수입니다.
CREATE FUNCTION [dbo].[SplitString]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255),
@ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @inp VARCHAR(MAX)
SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))
DECLARE @xml XML
SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'
DECLARE @ret VARCHAR(MAX)
SET @ret = (SELECT
el = split.el.value('.','varchar(max)')
FROM @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))
RETURN @ret
END
용법:
SELECT dbo.SplitString('Hello John Smith', ' ', 2)
결과:
John
이름과 성을 입력 할 수있는 간단한 솔루션
DECLARE @Name varchar(10) = 'John Smith'
-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))
-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))
내 경우에는 (그리고 다른 많은 경우에는 ...로 보입니다), 하나의 공백으로 구분 된 이름과 성 목록이 있습니다. 이것은 select 문 내에서 직접 사용하여 성과 이름을 구문 분석 할 수 있습니다.
-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable
나는 늦었지만 알고 있지만 최근 에이 요구 사항이 있었고 아래 코드를 생각해 냈습니다. 사용자 정의 함수를 사용할 수 없습니다. 도움이 되었기를 바랍니다.
SELECT
SUBSTRING(
SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)
),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')
)
글쎄, 내 것이 그렇게 간단하지는 않지만 여기에 쉼표로 구분 된 입력 변수를 개별 값으로 나누고 테이블 변수에 넣는 데 사용하는 코드가 있습니다. 공백을 기준으로 분할하고 결과를 얻기 위해 해당 테이블 변수에 대한 기본 SELECT 쿼리를 수행하도록이를 약간 수정할 수 있다고 확신합니다.
-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
AccountingCycle varchar(10)
)
DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int
SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
BEGIN
WHILE @intPosition > 0
BEGIN
SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
IF @vchAccountingCycle <> ''
BEGIN
INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
END
SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
END
END
개념은 거의 동일합니다. 다른 대안은 SQL Server 2005 자체에서 .NET 호환성을 활용하는 것입니다. 본질적으로 .NET에서 문자열을 분할 한 다음 저장 프로 시저 / 함수로 노출하는 간단한 메서드를 작성할 수 있습니다.
이것은 문자열에서 특정 토큰을 얻기 위해 내가 한 일입니다. (MSSQL 2008에서 테스트)
먼저 다음 함수를 작성 하십시오.
CREATE FUNCTION dbo.SplitStrings_Moden
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E42(N) AS (SELECT 1 FROM E4 a, E2 b),
cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1)))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
FROM cteStart s;
과
create FUNCTION dbo.getToken
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255),
@Pos int
)
RETURNS varchar(max)
as
begin
declare @returnValue varchar(max);
select @returnValue = tbl.Item from (
select ROW_NUMBER() over (order by (select null)) as id, * from dbo.SplitStrings_Moden(@List, @Delimiter)
) as tbl
where tbl.id = @Pos
return @returnValue
end
다음과 같이 사용할 수 있습니다.
select dbo.getToken('1111_2222_3333_', '_', 1)
1111을 반환
참고 URL : https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x
반응형
'Programing' 카테고리의 다른 글
| jQuery : 테이블의 행 수 계산 (0) | 2020.02.15 |
|---|---|
| 2D 점이 다각형 내에 있는지 어떻게 알 수 있습니까? (0) | 2020.02.15 |
| 현재 PowerShell 스크립트의 위치를 확인하는 가장 좋은 방법은 무엇입니까? (0) | 2020.02.14 |
| "?"후에 GET 매개 변수에 액세스하는 방법 (0) | 2020.02.14 |
| 텍스트 필드를 탐색하는 방법 (다음 / 완료 단추) (0) | 2020.02.14 |