Seperate value in a Column to different Segments or Columns in SSMS

Question

I have a table TABLE1 as below :

YEAR    |  GL_CODE
-------------------------------------------
2019    |  141.0000.1001.732155.0000.000.0000.
2019    |  141.0000.0000.143402.0000.131.0000.
2019    |  541.000.00.00.00.149104.0000.000.00
2019    |  541.101.04.00.00.731104.0000.000.00
2019    |  141.0000.0000.151310.0000.000.0000.
2019    |  541.102.06.00.16.714101.7098.000.00
2019    |  111.00000.0511.766701.0000.000.0000
2019    |  111.00000.0512.520111.5003.331.0000

I need it to separated to different Segments or Columns as shown below:

YEAR    |SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|SEGMENT5|SEGMENT6|SEGMENT7 |SEGMENT8|SEGMENT9
------------------------------------------------------------------------------------------
2019    |  141   |  0000  | 1001   |732155  | 0000   | 000    | 0000    | NULL   | NULL
2019    |  141   |  0000  | 0000   |143402  | 0000   | 131    | 0000    | NULL   | NULL
2019    |  541   |  000   | 00     | 00     | 00     |149104  | 0000    | 000    | 00
2019    |  541   |  101   | 04     | 00     | 00     |731104  | 0000    | 000    | 00
2019    |  141   |  0000  | 0000   |151310  | 0000   | 000    | 0000    | NULL   | NULL
2019    |  541   |  102   | 06     | 00     |714101  |714101  | 7098    | 000    | 00
2019    |  111   | 00000  | 0511   |766701  | 0000   | 000    | 0000    | NULL   | NULL
2019    |  111   | 00000  | 0512   |520111  | 5003   | 331    | 0000    | NULL   | NULL

Note that all the entries in doesnt have a fixed number of segment.

I tried by using :

select YEAR,PERIOD,SUBSTRING(GL_CODE,1,3) as segment1,
SUBSTRING(GL_CODE,
          charindex('.', GL_CODE, 1)+1,
          ((charindex('.',GL_CODE, (charindex('.', GL_CODE, 1)+1))+1)-(charindex('.', GL_CODE, 1)+1)-1)
          ) as segment2,  
SUBSTRING(GL_CODE,
          charindex('.',GL_CODE, (charindex('.', GL_CODE, 1)+1))+1,
          (charindex('.',GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1)-
               (charindex('.',GL_CODE, (  charindex('.', GL_CODE, 1)+1))+1)-1
          ) as segment3,  
SUBSTRING(GL_CODE,
          charindex('.',GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1,
          (charindex('.',GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1 )-(charindex('.',GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1)-1
          ) as segment4,
SUBSTRING(GL_CODE,
          charindex('.',GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1,(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.',GL_CODE, 1)+1))+1))+1))+1))+1)-(charindex('.',GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1)-1
          ) as segment5,
SUBSTRING(GL_CODE,
          charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, 
               (charindex('.',GL_CODE, 1)+1))+1))+1))+1))+1,
          (charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, 
              (charindex('.',    GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1)
                -(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.',GL_CODE, 1)+1))+1))+1))+1))+1)-1
          ) as segment6,
SUBSTRING(GL_CODE,
          charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, 
              (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1,
          (charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
              (charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1)
                -(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1)-1
          ) as segment7,
SUBSTRING(GL_CODE,
          charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
              (charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1,
          (charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
              (charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE,
              (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1))+1)
                -(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
              (charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1)-1
          ) as segment8
from TABLE1;

Im Getting values upto 6th Segment... After the 6th Segment I'm getting errors. Can anyone suggest a change in code or an alternative method?

Thanks in Advance

Akshay

Did you have a look at the duplicate I marked in your last [question](https://stackoverflow.com/questions/60053079/split-gl-code-column-into-different-columns-based-on-the-without-using-fun) — Thom A, Feb 06 '20 at 07:26
@Larnu : Yes i went through that... but i need this to be donw with SUBSTRING and CHARINDEX or similar basic function... because ithis needts to be run in a different Tool... which only accepts basic Functions — Dreamcatcher_AR, Feb 06 '20 at 07:29
The tag that that tool, and tell us that in the question. If you're so limited to the functions, it sounds like you're not using SQL Server too. — Thom A, Feb 06 '20 at 07:31
Also, like I mentioned in your prior quesion, SSMS, isn't a version of SQL Server, it's an IDE. The versions of SSMS you're using and SQL server are often unrelated. What version of SQL Server, not what version of SSMS, are you using? — Thom A, Feb 06 '20 at 07:33
@Larnu : The tool name is CCH Tagetik, and the SQl Server is SQl Server 2012. — Dreamcatcher_AR, Feb 06 '20 at 07:36

score 1 · Accepted Answer · answered Feb 06 '20 at 08:29

Create a UDF (User defined function)

 CREATE FUNCTION dbo.fn_Split
(   
    @str varchar(max),
    @delim char(1), 
    @columnCnt int = 50
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * 
    FROM (SELECT 
            nn = (nn - 1) / @columnCnt + 1, 
            nnn = 'value' + cast(((nn - 1) % @columnCnt) + 1 as varchar(10)), 
            value
        FROM (SELECT 
            nn = ROW_NUMBER() over (order by (select null)),
            value
            FROM string_split(@str, @delim) aa
            ) aa
        where nn > 0
    ) bb
    PIVOT  
    (  
    max(value) 
    FOR nnn IN (    
        value1, value2, value3, value4, value5, value6, value7, value8, value9  
     )  
    ) AS PivotTable 
)

then use the function in your query

CREATE TABLE #TBL ([YEAR] INT, GL_CODE VARCHAR(300))
INSERT INTO #TBL VALUES
(2019,'141.0000.1001.732155.0000.000.0000.'),
(2019,'141.0000.0000.143402.0000.131.0000.'),
(2019,'541.000.00.00.00.149104.0000.000.00'),
(2019,'541.101.04.00.00.731104.0000.000.00'),
(2019,'141.0000.0000.151310.0000.000.0000.'),
(2019,'541.102.06.00.16.714101.7098.000.00'),
(2019,'111.00000.0511.766701.0000.000.0000'),
(2019,'111.00000.0512.520111.5003.331.0000');

 SELECT 
[YEAR],
f.*
 FROM  #TBL 
 cross apply dbo.fn_Split(#TBL.GL_CODE, '.', DEFAULT) as f

OUTPUT

you can change the column names to yours

Zhorov · Answer 2 · 2020-02-07T12:12:05.480

Original answer:

Probably your error is Invalid length parameter passed to the LEFT or SUBSTRING function.. If you need to use only basic functions, then one possible approach is to fix the values in your GL_CODE column and add the necessary . chars.

SELECT
-- your complex statement here
FROM (
   SELECT YEAR, CONCAT(GL_CODE, REPLICATE('.', 9 - LEN(GL_CODE) + LEN(REPLACE(GL_CODE, '.', '')))) AS GL_CODE
   FROM Table1
) t

Update:

If you can use SQL Server 2016+, you may try an approach, based on JSON. The idea is to transform the text into a valid JSON array (141.0000.1001.732155.0000.000.0000. is transformed into [["141","0000","1001","732155","0000","000","0000"]]) and then parse this JSON text using OPENJSON() with explicit schema. Note, that STRING_SPLIT() shouldn't be an option, because the order of the splitted substrings is not guaranteed to match the order of the substrings in the input string.

Table:

CREATE TABLE Data (
   [YEAR] int, 
   GL_CODE varchar(100)
)
INSERT INTO Data
   ([YEAR], GL_CODE)
VALUES
   (2019, '141.0000.1001.732155.0000.000.0000.'),
   (2019, '141.0000.0000.143402.0000.131.0000.'),
   (2019, '541.000.00.00.00.149104.0000.000.00'),
   (2019, '541.101.04.00.00.731104.0000.000.00'),
   (2019, '141.0000.0000.151310.0000.000.0000.'),
   (2019, '541.102.06.00.16.714101.7098.000.00'),
   (2019, '111.00000.0511.766701.0000.000.0000'),
   (2019, '111.00000.0512.520111.5003.331.0000');

Statement:

SELECT 
   d.[YEAR],
   j.*
FROM Data d   
CROSS APPLY OPENJSON(CONCAT(
      '[["',
      REPLACE(
         CASE 
            WHEN RIGHT(d.GL_CODE, 1) = '.' THEN LEFT(d.GL_CODE, LEN(d.GL_CODE) - 1)
            ELSE GL_CODE
         END, '.', '","'),
      '"]]'
   )
) WITH (
   SEGMENT1 varchar(10) '$[0]',
   SEGMENT2 varchar(10) '$[1]',
   SEGMENT3 varchar(10) '$[2]',
   SEGMENT4 varchar(10) '$[3]',
   SEGMENT5 varchar(10) '$[4]',
   SEGMENT6 varchar(10) '$[5]',
   SEGMENT7 varchar(10) '$[6]',
   SEGMENT8 varchar(10) '$[7]',
   SEGMENT9 varchar(10) '$[8]'
) j

Result:

YEAR    SEGMENT1    SEGMENT2    SEGMENT3    SEGMENT4    SEGMENT5    SEGMENT6    SEGMENT7    SEGMENT8    SEGMENT9
2019    141         0000        1001        732155      0000        000         0000        
2019    141         0000        0000        143402      0000        131         0000        
2019    541         000         00          00          00          149104      0000        000         00
2019    541         101         04          00          00          731104      0000        000         00
2019    141         0000        0000        151310      0000        000         0000        
2019    541         102         06          00          16          714101      7098        000         00
2019    111         00000       0511        766701      0000        000         0000        
2019    111         00000       0512        520111      5003        331         0000

Seperate value in a Column to different Segments or Columns in SSMS

2 Answers2