복붙노트

[SQL] 별도 쉼표 SQL 서버 테이블 값 저장소를 분리

SQL

별도 쉼표 SQL 서버 테이블 값 저장소를 분리

I는 입력으로서 콤마 분리 된 값을 얻는다 저장 프로 시저를 구비하고있다. 나는 그것을 분리 할 필요가 및 개별 행과 같은 테이블에 저장해야합니다.

SP는 대한 입력을 보자 :

Rule_ID  ListType_ID  Values
1        2            319,400,521,8465,2013

나는 형식 아래에서 DistributionRule_x_ListType라는 테이블에 저장해야합니다

Rule_ID  ListType_ID  Value
1        2            319
1        2            400
1        2            521
1        2            8465
1        2            2013

내 SP의 모습은 다음과 같은 :

ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(@Rule_ID int,
@ListType_ID int,
@Values VARCHAR(MAX)=NULL
)
AS
BEGIN

    INSERT INTO DistributionRule_x_ListType (Rule_ID,ListType_ID,Value)
    VALUES (@Rule_ID,@ListType_ID,@Values)

END

해결법

  1. ==============================

    1.이 유사한 분할 기능을 만들어야합니다 :

    이 유사한 분할 기능을 만들어야합니다 :

    create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
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;

    그런 다음 저장 프로 시저에, 당신은 당신의 문자열을 분할 함수를 호출합니다 :

    ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(
  @Rule_ID int,
  @ListType_ID int,
  @Values VARCHAR(MAX)=NULL
)
AS
BEGIN

    INSERT INTO DistributionRule_x_ListType (Rule_ID, ListType_ID, Value)
    SELECT @Rule_ID, @ListType_ID, items
    FROM [dbo].[Split] (@Values, ',')  -- call the split function 

END

    당신이 저장 프로 시저를 실행하면 값을 분할하고 테이블에 여러 행을 삽입합니다 :

    exec spInsertDistributionRuleListType 1, 2, '319,400,521,8465,2013';

    데모와 SQL 바이올린을 참조하십시오. 이것은 다음과 같은 결과를 삽입합니다 :

    | RULE_ID | LISTTYPE_ID | VALUE |
---------------------------------
|       1 |           1 |    10 |
|       1 |           2 |   319 |
|       1 |           2 |   400 |
|       1 |           2 |   521 |
|       1 |           2 |  8465 |
|       1 |           2 |  2013 |
  2. ==============================

    2.당신은 같은 charIndex에 함께 할 수 있습니다

    당신은 같은 charIndex에 함께 할 수 있습니다

    DECLARE @id VARCHAR(MAX)

SET @id = @Values          --'319,400,521,8465,2013,'

WHILE CHARINDEX(',', @id) > 0 
BEGIN

    DECLARE @tmpstr VARCHAR(50)
     SET @tmpstr = SUBSTRING(@id, 1, ( CHARINDEX(',', @id) - 1 ))

    INSERT  INTO DistributionRule_x_ListType
            ( Rule_ID ,
              ListType_ID ,
              Value
            )
    VALUES  ( @Rule_ID ,
              @ListType_ID ,
              @tmpstr)
            )
    SET @id = SUBSTRING(@id, CHARINDEX(',', @id) + 1, LEN(@id))
END
  3. ==============================

    3.당신은 dbo.Split 기능이없는이 작업을 수행 할 수 있습니다.

    당신은 dbo.Split 기능이없는이 작업을 수행 할 수 있습니다.

    여기에 귀하의 샘플 데이터입니다

    SELECT * INTO #TEMP
FROM
(
     SELECT 1  Rule_ID, 2 ListType_ID, '319,400,521,8465,2013' [Values]
     UNION ALL
     SELECT 1  , 3 , '100,200' 
)TAB

    이제 다음 쿼리를 실행하고 각 RULE_ID 및 ListType_ID에 대한 모든 쉼표로 구분 된 값을 선택합니다.

    SELECT [Rule_ID],ListType_ID,
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Values' 
FROM  
(
     SELECT [Rule_ID],ListType_ID,
     CAST ('<M>' + REPLACE([Values], ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM #TEMP     
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)
  4. ==============================

    4.

    select Rule_ID ,ListType_ID,Values as value
FROM table  
    CROSS APPLY STRING_SPLIT(Values, ',');
  5. ==============================

    5.당신은 문자열을 분할 테이블 VAR을 반환하는 UDF를 만들 수 있습니다. 우리는 MSSQL2005에 성공적으로 다음을 사용했다.

    당신은 문자열을 분할 테이블 VAR을 반환하는 UDF를 만들 수 있습니다. 우리는 MSSQL2005에 성공적으로 다음을 사용했다.

    CREATE FUNCTION [dbo].[fn_explode] (
    @str_separator NVARCHAR(255),
    @str_string VARCHAR(4000)
)
RETURNS @ret_string_parts TABLE (str_value varchar(4000))
AS
BEGIN
    DECLARE @intPos INT
    DECLARE @intLengthString INT
    DECLARE @intTempPatIndex INT
    DECLARE @intLengthSeparator INT

    SET @str_string = @str_string + @str_separator

    SET @intPos = 0
    SET @intLengthString = LEN(@str_string)
    SET @intLengthSeparator = LEN(@str_separator)



    IF PATINDEX ( '%' + @str_separator + '%' , @str_string ) <= 0 BEGIN
        INSERT INTO @ret_string_parts
        SELECT @str_string
        RETURN
    END

    IF @str_separator =  @str_string BEGIN
        INSERT INTO @ret_string_parts
        SELECT @str_string
        RETURN
    END

    WHILE @intPos <= @intLengthString
    BEGIN
        SET @intTempPatIndex = PATINDEX('%' + @str_separator + '%', SUBSTRING(@str_string, @intPos,@intLengthString))
        IF @intTempPatIndex = 0 BEGIN
            INSERT INTO @ret_string_parts
            SELECT SUBSTRING(@str_string, @intPos, @intLengthString)
            BREAK
        END
        ELSE BEGIN
            IF @intPos = 0 BEGIN
                INSERT INTO @ret_string_parts
                SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex)
                SET @intPos = @intPos + @intTempPatIndex + @intLengthSeparator
            END
            ELSE BEGIN
                INSERT INTO @ret_string_parts
                SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex-1)
                SET @intPos = @intPos + @intTempPatIndex + (@intLengthSeparator-1)
            END

        END
    END

    RETURN
END
  6. ==============================

    6.@bluefeet 응답을 완료하면, 당신은 또한 여러 열에서 여러 값을 저장하기 위해 CSV 문자열을 사용할 수 있습니다 :

    @bluefeet 응답을 완료하면, 당신은 또한 여러 열에서 여러 값을 저장하기 위해 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

    7. from https://stackoverflow.com/questions/14811316/separate-comma-separated-values-and-store-in-table-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버 : 드롭 테이블 캐스케이드 동등한?  (0) 2020.05.03
[SQL] SQL - 조건이 문제가 WHERE의 순서를합니까?  (0) 2020.05.03
[SQL] 외래 키 등을 사용하여 복합 기본 키  (0) 2020.05.03
[SQL] SQL의 가난한 저장 프로 시저 실행 계획 성능 - 매개 변수 스니핑  (0) 2020.05.03
[SQL] 어떻게 연도를 무시 날짜 계산을해야합니까?  (0) 2020.05.03
,

티스토리툴바