Split String in SQL Server to a Maximum Length, Returning Each as a Row

Split string in SQL Server to a maximum length, returning each as a row

Try something like this. May be your can create a SQL function of following implementation.

DECLARE @Str VARCHAR(1000)
SET @Str = '2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract for this information.'

DECLARE @End INT
DECLARE @Split INT

SET @Split = 100

declare @SomeTable table
(
  Content varchar(3000)
)

WHILE (LEN(@Str) > 0)
BEGIN
    IF (LEN(@Str) > @Split)
    BEGIN
        SET @End = LEN(LEFT(@Str, @Split)) - CHARINDEX(' ', REVERSE(LEFT(@Str, @Split)))
        INSERT INTO @SomeTable VALUES (RTRIM(LTRIM(LEFT(LEFT(@Str, @Split), @End))))
        SET @Str = SUBSTRING(@Str, @End + 1, LEN(@Str))
    END
    ELSE
    BEGIN
        INSERT INTO @SomeTable VALUES (RTRIM(LTRIM(@Str)))
        SET @Str = ''
    END
END

SELECT *
FROM @SomeTable

Output will be like this:

2012-04-24 Change request #3 for the contract per terms and conditions and per John Smith in the
PSO department  Customer states terms should be Net 60 not Net 30.  Please review signed contract
for this information.

Split String into given length but do not split word

Assuming you have a table of addresses, I'd use a recursive CTE.

On each iteration, find the last possible space to break on, then start the next iteration for the character after the space.

• take 31 characters
• reverse them
• find the position of the first space

Extra care to be taken for corner cases:

• The remaining string to be searched is less than 30 characters
• The current string being searched has no space in the first 31 characters

Using the following test data...

CREATE TABLE test (
  address   VARCHAR(MAX)
);

INSERT INTO
  test
VALUES
  ('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTown Upper1Location Another5 NewYork'),
  ('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTownxx Upper1LocationUpper1LocationUpper1Location Another5 NewYork'),
  ('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTownxx Upper1LocationUpper1LocationUpper1Location Another5 NewYork x')

;

Using the following CTE...

DECLARE @chars BIGINT = 30;

WITH
  parts AS
(
  SELECT
    address,
    LEN(address)        AS length,
    CAST(0 AS BIGINT)   AS last_space,
    CAST(1 AS BIGINT)   AS next,
    address             AS fragment
  FROM
    test

  UNION ALL

  SELECT
    parts.address,
    parts.length,
    last_space.pos,
    parts.next + COALESCE(last_space.pos, @chars),
    SUBSTRING(parts.address, parts.next, COALESCE(last_space.pos - 1, @chars))
  FROM
    parts
  CROSS APPLY
  (
    SELECT
      @chars + 2
      -
      NULLIF(
        CHARINDEX(
          ' ',
          REVERSE(
            SUBSTRING(
              parts.address + ' ',
              parts.next,
              @chars + 1
            )
          )
        )
        , 0
      )
  )
    last_space(pos)
  WHERE
    parts.next <= parts.length
)

SELECT
  *, len(fragment) AS chars
FROM
  parts
WHERE
  next > 1
ORDER BY
  address,
  next

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=acd11f2bc73e5036bd82498ecf14b08f

Split unlimited length SQL String into two columns

Please try the following solution based on XML and XQuery.

It allows to get odd vs. even tokens in the input string.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, descr varchar(max), meter int);

DECLARE @string varchar(max) = 'desc1$100$desc2$200';
DECLARE @separator CHAR(1) = '$';

DECLARE @xmldata XML = TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(@string, @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)

INSERT INTO  @tbl (descr, meter)
SELECT c.value('(./text())[1]', 'VARCHAR(30)') AS descr
    , c.value('(/root/*[sql:column("seq.pos")]/text())[1]', 'INT') AS meter
FROM @xmldata.nodes('/root/*[position() mod 2 = 1]') AS t(c)
   CROSS APPLY (SELECT t.c.value('let $n := . return count(/root/*[. << $n[1]]) + 2','INT') AS pos
         ) AS seq;

-- Test
SELECT * FROM  @tbl;

Output

+----+-------+-------+
| ID | descr | meter |
+----+-------+-------+
|  1 | desc1 |   100 |
|  2 | desc2 |   200 |
+----+-------+-------+

T-SQL split string

I've used this SQL before which may work for you:-

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

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

SQL Server : Split string to row

With this TVF, you can supply the string to be split and delimiter. Furthermore, you get the sequence number which can be very useful for secondary processing.

Select [CODE COMBINATION]
      ,[USER] = B.RetVal
 From  YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.[USER],';') B

Returns

The Parse UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

---

Now, another option is the Parse-Row UDF. Notice we return the parsed string in one row. Currently 9 positions, but it is easy to expand or contract.

Select [CODE COMBINATION]
      ,B.*
 From  YourTable A
 Cross Apply [dbo].[udf-Str-Parse-Row](A.[USER],';') B

Returns

The Parse Row UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    Select Pos1 = xDim.value('/x[1]','varchar(max)')
          ,Pos2 = xDim.value('/x[2]','varchar(max)')
          ,Pos3 = xDim.value('/x[3]','varchar(max)')
          ,Pos4 = xDim.value('/x[4]','varchar(max)')
          ,Pos5 = xDim.value('/x[5]','varchar(max)')
          ,Pos6 = xDim.value('/x[6]','varchar(max)')
          ,Pos7 = xDim.value('/x[7]','varchar(max)')
          ,Pos8 = xDim.value('/x[8]','varchar(max)')
          ,Pos9 = xDim.value('/x[9]','varchar(max)')
     From (Select Cast('<x>' + Replace(@String,@Delimiter,'</x><x>')+'</x>' as XML) as xDim) A
)
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')

Split and get second row as value

Instead of using STRING_SPLIT you can convert your string to XML and then use .value to retrieve the 2nd element:

SELECT CAST('<t>' + REPLACE('Name1~Name2~Name3' , '~','</t><t>') + '</t>' AS XML).value('/t[2]','varchar(50)')

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

---

Related Topics