How to Split String by Character into Separate Columns in SQL Server

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

How to Split String by Character into Separate Columns in SQL Server

There are probably several different ways to do it, some uglier than others. Here's one:

(Note: dat = the string of characters)

select *,
  substring(dat,1,charindex('-',dat)-1) as Section,
  substring(dat,charindex('-',dat)+1,charindex('-',dat)-1) as TownShip,
  reverse(substring(reverse(dat),0,charindex('-',reverse(dat)))) as myRange
from myTable

how to separate string into different columns?

Instead of using split function there is a function called ParseName which returns the specified part of the object which spilts the string delimated by .
Please go through the ParseName link which helped me in writing this query

Declare @Sample Table
(MachineName varchar(max))

Insert into @Sample
values 
('Ab bb zecos'),('a Zeng')


  SELECT 
  Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 1)) As [M1]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 2)) As [M2]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 3)) As [M3]

  FROM  (Select MachineName from @Sample
  ) As [x]

SQL server split string into columns by delimiter (dynamic length)

An xml-based solution

declare @tmp table (STRING varchar(500))

insert into @tmp
values
 ('AA.0.HJ')
,('AABBCC.099.0')
,('0.91.JAH21')

;WITH Splitted
AS (
    SELECT STRING
        ,CAST('<x>' + REPLACE(STRING, '.', '</x><x>') + '</x>' AS XML) AS Parts
    FROM @tmp
    )
SELECT STRING
    ,Parts.value(N'/x[1]', 'varchar(50)') AS [First]
    ,Parts.value(N'/x[2]', 'varchar(50)') AS [Second]
    ,Parts.value(N'/x[3]', 'varchar(50)') AS [Third] 
FROM Splitted;

Output:

Sample Image

How to split a string with multiple special characters or delimiters into separate fragments using SQL?

If you're using SQL Server 2017+ it provides translate that can help here combined with string_split:

with sample as (
 select 'abcd_45dl/beta3,test' StringCol union all
 select 'a56d/beta_46ab'
)
select *
from sample
cross apply String_Split(Translate(StringCol,'_/',',,'),',')

Split column value into separate columns based on length

siddesh, although this question lacks of everything I want to point some things out and help you (as you are an unexperienced SO-user):

First I set up a minimal reproducible exampel. This is on you the next time.

I'll start with a declared table with some rows inserted.

We on SO can copy'n'paste this into our environment which makes it easy to answer.

DECLARE @tbl TABLE(ID INT IDENTITY, YourCSVString VARCHAR(MAX));
INSERT INTO @tbl VALUES('1 this is long text, 2 some second fragment, 3 third fragment, 4 adfjksdahfljsadhfjhadlfhasdjks alsdjfsadhf k, 5 halksjfh asdkf ')
                      ,('1 this is other long text, 2 some second fragment to show that this works with tabular data, 3 again a third fragment, 4 adfjksdahfljsadhfjhadlfhasdjks alsdjfsadhf k, 5 halksjfh asdkf ');

--This is, what you actually need:

SELECT fkID = t.ID
      ,B.fragmentPosition
      ,B.fragmentContent 
      ,C.framgentLength
FROM @tbl t
CROSS APPLY OPENJSON(CONCAT(N'["',REPLACE(t.YourCSVString,N',','","'),'"]')) A
CROSS APPLY(VALUES(A.[key],TRIM(A.[value]))) B(fragmentPosition,fragmentContent)
CROSS APPLY(VALUES(LEN(B.fragmentContent))) C(framgentLength);

The result should be stored within a physical table, where the fkID points to the ID of the original row and the fragmentPosition stores the order. fkID and fragmentPosition should be a combined unique key.

If you really want to do, what you are suggesting in your question (not recommended!) you can try something along this:

DECLARE @maxPerColumn INT=75;  --You can set the portion's max size, in your case 2000.

WITH cte AS
(
    SELECT fkID = t.ID
          ,B.fragmentPosition
          ,B.fragmentContent 
          ,C.framgentLength
    FROM @tbl t
    CROSS APPLY OPENJSON(CONCAT(N'["',REPLACE(t.YourCSVString,N',','","'),'"]')) A
    CROSS APPLY(VALUES(A.[key],TRIM(A.[value]))) B(fragmentPosition,fragmentContent)
    CROSS APPLY(VALUES(LEN(B.fragmentContent))) C(framgentLength)
)
,recCTE AS
(
    SELECT * 
          ,countPerColumn = 1
          ,columnCounter = 1
          ,sumLength = LEN(fragmentContent)
          ,growingString = CAST(fragmentContent AS NVARCHAR(MAX)) 
    FROM cte WHERE fragmentPosition=0

    UNION ALL
    SELECT r.fkID
          ,cte.fragmentPosition
          ,cte.fragmentContent
          ,cte.framgentLength
          ,CASE WHEN A.newSumLength>@maxPerColumn THEN 1 ELSE r.countPerColumn + 1 END
          ,r.columnCounter + CASE WHEN A.newSumLength>@maxPerColumn THEN 1 ELSE 0 END
          ,CASE WHEN A.newSumLength>@maxPerColumn THEN LEN(cte.fragmentContent) ELSE newSumLength END
          ,CASE WHEN A.newSumLength>@maxPerColumn THEN cte.fragmentContent ELSE CONCAT(r.growingString,N', ',cte.fragmentContent) END
    FROM cte
    INNER JOIN recCTE r ON r.fkID=cte.fkID AND r.fragmentPosition+1=cte.fragmentPosition 
    CROSS APPLY(VALUES(r.sumLength+LEN(cte.fragmentContent))) A(newSumLength)
)
SELECT TOP 1 WITH TIES 
       fkID
      ,growingString
      ,LEN(growingString)
FROM recCTE 
ORDER BY ROW_NUMBER() OVER(PARTITION BY fkID,columnCounter ORDER BY countPerColumn DESC );

The result

fkID    pos Content
1       2   1 this is long text, 2 some second fragment, 3 third fragment
1       4   4 adfjksdahfljsadhfjhadlfhasdjks alsdjfsadhf k, 5 halksjfh asdkf
2       0   1 this is other long text
2       1   2 some second fragment to show that this works with tabular data
2       3   3 again a third fragment, 4 adfjksdahfljsadhfjhadlfhasdjks alsdjfsadhf k
2       4   5 halksjfh asdkf

The idea in short:

The first cte does the splitting (as above)
The recursive cte will iterate down the string and do the magic.
The final SELECT uses a hack with TOP 1 WITH TIES together with an ORDER BY ROW_NUMBER() OVER(...). This will return the highest intermediate result only.

Hint: Don't do this...

UPDATE

Just for fun:

You can replace the final SELECT with this

,getPortions AS
(
    SELECT TOP 1 WITH TIES 
           fkID
          ,fragmentPosition
          ,growingString
          ,LEN(growingString) portionLength
    FROM recCTE 
    ORDER BY ROW_NUMBER() OVER(PARTITION BY fkID,columnCounter ORDER BY countPerColumn DESC )
)
SELECT p.*
FROM
(
    SELECT fkID
          ,CONCAT(N'col',ROW_NUMBER() OVER(PARTITION BY fkID ORDER BY fragmentPosition)) AS ColumnName
          ,growingString
    FROM getPortions
) t
PIVOT(MAX(growingString) FOR ColumnName IN(col1,col2,col3,col4,col5)) p;

This will return exactly what you are asking for.

But - as said before - this is against all rules of best practice...