locked
sql string split RRS feed

  • Question

  • hi ;

    I have a CODE field that included 6-1-A-B and I have query 

    SELECT CODE ,CODE1,CODE2,CODE3,CODE4 FROM TABLE

    I want to extract the string field ('-') , so the result is

    CODE        CODE1    CODE2    CODE3     CODE4

    6-1-A-B        6             1            A            B

    thanks in advance

    Friday, January 18, 2019 3:22 PM

Answers

  • You can use a split function (either built-in or UDF).

    For your sample you can use parsename function as well:

    Insert into  yourtable (CODE1,CODE2,CODE3,CODE4)
    select parsename(Replace('6-1-A-B','-','.'),4)
    ,parsename(Replace('6-1-A-B','-','.'),3)
    ,parsename(Replace('6-1-A-B','-','.'),2)
    ,parsename(Replace('6-1-A-B','-','.'),1)

    • Marked as answer by Aly14 Friday, January 18, 2019 3:51 PM
    Friday, January 18, 2019 3:29 PM

All replies

  • You can use a split function (either built-in or UDF).

    For your sample you can use parsename function as well:

    Insert into  yourtable (CODE1,CODE2,CODE3,CODE4)
    select parsename(Replace('6-1-A-B','-','.'),4)
    ,parsename(Replace('6-1-A-B','-','.'),3)
    ,parsename(Replace('6-1-A-B','-','.'),2)
    ,parsename(Replace('6-1-A-B','-','.'),1)

    • Marked as answer by Aly14 Friday, January 18, 2019 3:51 PM
    Friday, January 18, 2019 3:29 PM
  • There are several ways to do this.  

    Here is one:

    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('.','nvarchar(MAX)') as item
              FROM  @xml.nodes('/t') as records(r)
              RETURN
            END
    GO
    DECLARE @t TABLE (CODE varchar(max));
    
    INSERT INTO @t VALUES ('6-1-A-B');
    
    SELECT 
        t.CODE,
        MAX(CASE WHEN id = 1 THEN s.[val] ELSE NULL END) AS CODE1,
        MAX(CASE WHEN id = 2 THEN s.[val] ELSE NULL END) AS CODE2,
        MAX(CASE WHEN id = 3 THEN s.[val] ELSE NULL END) AS CODE3,
        MAX(CASE WHEN id = 4 THEN s.[val] ELSE NULL END) AS CODE4
    
    FROM @t as t
        CROSS APPLY dbo.split(t.CODE,'-') as s
    GROUP BY t.CODE;
    

    Friday, January 18, 2019 3:30 PM
  • create table #mytable(Code varchar(50))
    Insert into #mytable values('6-1-A-B'),('4-1-F-T')
    
    
    SELECT DISTINCT 
    
    Code
    , S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS CODE1
    ,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS CODE2
    ,S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS CODE3
    ,S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS CODE4
    FROM
    (
    SELECT *,CAST (N'<H><r>' + REPLACE(Code, '-', '</r><r>')  + '</r></H>' AS XML) AS [vals]
    FROM #mytable) d 
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    
    --Option 3  
    --Use a SPILT function
    
    --Option 4
    --Use charindex to manipulate the string
    
    drop table #mytable



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Friday, January 18, 2019 3:37 PM
  • Just to be clear.

    parsename() will only parses 4 segments separated by period.  I would not recommend this for general purpose splitting of strings.

    Friday, January 18, 2019 5:36 PM
  • whats your suggest.
    Friday, January 18, 2019 5:40 PM
  • See my response below.

    Friday, January 18, 2019 6:07 PM
  • --SQL Server 2016, 2017
     
    create table source (CODE varchar(100))
    Insert into Source values('6-1-A-B')
    
    --Insert into  yourtable (CODE, CODE1,CODE2,CODE3,CODE4)
    Select  s.CODE
    , Max(Case when rn=1 then value end) CODE1
    , Max(Case when rn=2 then value end) CODE2
    , Max(Case when rn=3 then value end) CODE3
    , Max(Case when rn=4 then value end) CODE4
    from Source s
    Cross apply (
    SELECT ss.[value], ROW_NUMBER() OVER (PARTITION BY s.CODE ORDER BY s.CODE ) AS RN
    FROM string_Split(CODE,'-') AS ss
    ) as d
    
    Group by s.CODE
    
    Drop table source

    Friday, January 18, 2019 6:28 PM
  • The built in string_split() is not a good option if you depend on the order of the values.  The order in the code above is "undefined" and not guaranteed to be in the same order for code1-4.  

    Tuesday, January 22, 2019 6:12 PM