[SQL] 어떻게 변환 쉼표로 2005 SQL 서버에서 테이블 레코드에 NVARCHAR을 분리?
SQL어떻게 변환 쉼표로 2005 SQL 서버에서 테이블 레코드에 NVARCHAR을 분리?
내가 좋아하는 쉼표로 분리 된 ID의 목록을 가지고 :
1,17,25,44,46,67,88
내가 좋아하는 (임시 테이블에) 테이블 레코드로 변환 할
#tempTable
number_
--------
1
17
25
44
46
67
88
그것은 함수, 테이블 반환 한 가능합니다?
나는이 왜 싶어? 나뿐만 같은 다른 테이블 (들) (저장 프로 시저로) 절을 가입 INNER에 사용할 :
SELECT a,b,c FROM T1
INNER JOIN functionNameWhichReturnsTable
ON functionNameWhichReturnsTable.number_ = T1.a
내가 형 NVARCHAR의 매개 변수를 받아 저장 프로 시저를 사용하기 때문에 나는 IN을 사용할 수 없습니다. 즉, 매개 변수는 ID의 목록을 제공합니다.
감사합니다
해결법
-
==============================
1.별도의 쉼표 사용 가능한 복제는 SQL 서버에서 테이블에 값 저장을 분리.
별도의 쉼표 사용 가능한 복제는 SQL 서버에서 테이블에 값 저장을 분리.
표에 쉼표로 구분 된 값에서 정확한 하나를 시도하십시오 :
CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1)) RETURNS @OutputTable TABLE ( [String] VARCHAR(10) ) AS BEGIN DECLARE @String VARCHAR(10) WHILE LEN(@StringInput) > 0 BEGIN SET @String = LEFT(@StringInput, ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1), LEN(@StringInput))) SET @StringInput = SUBSTRING(@StringInput, ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0), LEN(@StringInput)) + 1, LEN(@StringInput)) INSERT INTO @OutputTable ( [String] ) VALUES ( @String ) END RETURN END GO
XML을 사용하여 다른 방법으로 요구 사항을 확인합니다 :
DECLARE @param NVARCHAR(MAX) SET @param = '1:0,2:1,3:1,4:0' SELECT Split.a.value('.', 'VARCHAR(100)') AS CVS FROM ( SELECT CAST ('<M>' + REPLACE(@param, ',', '</M><M>') + '</M>' AS XML) AS CVS ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
-
==============================
2.여기에 기능이나 XML을 필요로하지 않는 트릭입니다.
여기에 기능이나 XML을 필요로하지 않는 트릭입니다.
기본적으로 문자열은 임시 테이블에 대한 단일 삽입 문으로 변환됩니다.
임시 테이블은 추가 처리를 위해 사용될 수있다.
IF OBJECT_ID('tempdb..#tmpNum') IS NOT NULL DROP TABLE #tmpNum; CREATE TABLE #tmpNum (num int); DECLARE @TEXT varchar(max) = '1,17,25,44,46,67,88'; DECLARE @InsertStatement varchar(max); SET @InsertStatement = 'insert into #tmpNum (num) values ('+REPLACE(@TEXT,',','),(')+');'; EXEC (@InsertStatement); -- use the temp table SELECT * FROM YourTable t WHERE t.id IN (SELECT DISTINCT num FROM #tmpNum);
이 방법 (1000)의 값까지 사용 가능하다. 1,000 때문에 로우 값 표현식의 최대 한계이다.
또한, 스튜어트 워스는 지적했다. 이 방법은 동적 SQL을 사용하므로, 코드 주입 조심 및 사용자 입력에 따라 문자열을 사용하지 않는다.
사이드 노트
MS SQL 서버 2016에서 시작, 하나는 단순히 STRING_SPLIT 기능을 사용할 수 있습니다.
DECLARE @TEXT varchar(max); SET @TEXT = '1,17,25,44,46,67,88'; SELECT t.* FROM YourTable t JOIN (SELECT DISTINCT CAST(value AS INT) num FROM STRING_SPLIT(@TEXT, ',')) nums ON t.id = nums.num;
-
==============================
3.답변을 완료하면, 당신은 또한 여러 열에서 여러 값을 저장하기 위해 CSV 문자열을 사용할 수 있습니다 :
답변을 완료하면, 당신은 또한 여러 열에서 여러 값을 저장하기 위해 CSV 문자열을 사용할 수 있습니다 :
--input sql text declare @text_IN varchar(max) ='text1, text1.2, text1.3, 1, 2010-01-01\r\n text2, text2.2, text2.3, 2, 2016-01-01'
행에 csv 파일을 분할 :
declare @temptable table (csvRow varchar(max)) declare @DelimiterInit varchar(4) = '\r\n' declare @Delimiter varchar(1) = '|' declare @idx int declare @slice varchar(max) set @text_IN = REPLACE(@text_IN,@DelimiterInit,@Delimiter) select @idx = 1 if len(@text_IN)<1 or @text_IN is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@text_IN) if @idx!=0 set @slice = left(@text_IN,@idx - 1) else set @slice = @text_IN if(len(@slice)>0) insert into @temptable(csvRow) values(@slice) set @text_IN = right(@text_IN,len(@text_IN) - @idx) if len(@text_IN) = 0 break end
열로 분할 행 :
;WITH XMLTable (xmlTag) AS ( SELECT CONVERT(XML,'<CSV><champ>' + REPLACE(csvRow,',', '</champ><champ>') + '</champ></CSV>') AS xmlTag FROM @temptable ) SELECT RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[1]','varchar(max)'))) AS Column1, RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[2]','varchar(max)'))) AS Column2, RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[3]','varchar(max)'))) AS Column3, RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[4]','int'))) AS Column4, RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[5]','datetime'))) AS Column5 FROM XMLTable
-
==============================
4.다음 작품 :
다음 작품 :
declare @parStoreNo As varchar(8000) = '1,2,3,4' CREATE TABLE #parStoreNo (StoreNo INT)-- drop #parStoreNo declare @temptable VARCHAR(1000) = @parStoreNo declare @SQL VARCHAR(1000) SELECT @SQL = CONVERT(VARCHAR(1000),' select ' + REPLACE(ISNULL(@temptable,' NULL '),',', ' AS Col UNION ALL SELECT ')) INSERT #parStoreNo (StoreNo) EXEC (@SQL)
-
==============================
5.나는 다음과 같이 XML 기능을 사용하고 있습니다 ...
나는 다음과 같이 XML 기능을 사용하고 있습니다 ...
DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894' Declare @x XML select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml) select t.value('.', 'int') as inVal from @x.nodes('/A') as x(t)
별도의 기능과 시저를 만들 필요가 있기 때문에 나는이 선호합니다. 또한 내가 가장 선호하는 선택 하 동적 SQL 쿼리 할 필요가 없습니다. 쉼표 표에 문자열을 분리 변환
-
==============================
6.
DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832' DECLARE @x XML select @x = cast('<A>'+ replace(@str,',','</A><A>')+ '</A>' as xml) select t.value('.', 'int') as inVal from @x.nodes('/A') as x(t)
-
==============================
7.이 코드를 사용해보십시오
이 코드를 사용해보십시오
SELECT RTRIM(part) as part INTO Table_Name FROM dbo.splitstring(@Your_Comma_string,',')
다음과 같이 분할 문자열 기능입니다
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
from https://stackoverflow.com/questions/15585632/how-to-convert-comma-separated-nvarchar-to-table-records-in-sql-server-2005 by cc-by-sa and MIT license
'SQL' 카테고리의 다른 글
[SQL] 날짜에 일을 추가 MYSQL (0) | 2020.04.20 |
---|---|
[SQL] 매개 변수화 된 쿼리가 공급되지 않은 매개 변수를 기대 (0) | 2020.04.20 |
[SQL] (전치?)를 폭발 스파크 SQL 테이블에서 여러 열을 (0) | 2020.04.20 |
[SQL] SQL : 변수에 따라 선택 동적 열 이름 (0) | 2020.04.20 |
[SQL] 어떻게 스파크 RDD에 대한 SQL ROW_NUMBER 동등한를받을 수 있나요? (0) | 2020.04.20 |