Splitting the String in SQL Server

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')

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 a string after specific character in SQL Server and update this value to specific column

Try this:

UPDATE YourTable
SET Col2 = RIGHT(Col1,LEN(Col1)-CHARINDEX('/',Col1))

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 a string with varying factors in SQL Server

This is tagged with [sql-server-2012] - what a pity... With v2016+ you could call for STRING_SPLIT or even JSON methods... The following is a rather hacky approach but works - at least with your provided test data...

Create a mockup-table (please do this yourself the next time).

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(100));
INSERT INTO @tbl VALUES
 ('["2","3","4"]')
,('["1","2","3"]')
,('[]')
,('["Select option B","Select option C","Select option D"]')
,('["Moderate","Heavy","Heavy, Big & Abnormal"]');

--This is the query:

SELECT t.ID
      --,t.YourString 
      ,C.Separated.value('text()[1]','nvarchar(max)') AS Parted
FROM @tbl t
CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(YourString,'"','"'),'["',''),'"]','')) A(replaced)
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT A.replaced [*] FOR XML PATH('')),'","','</x><x>') + '</x>' AS XML)) B(casted)
CROSS APPLY B.casted.nodes('/x') C(Separated);

The idea in short:

First of all I use multiple REPLACE() to clean and harmonise your data. The second CROSS APPLY will then use XML to split up your strings by replacing each comma together with the quotes! with XML tags. Thus we can prevent splitting at internal commas. But before, we have to use FOR XML on the orginal string, to allow characters such as the & in Big & Abnormal. The rest ist rather easy XPath/XQuery.

The result

+----+-----------------------+
| ID | Parted                |
+----+-----------------------+
| 1  | 2                     |
+----+-----------------------+
| 1  | 3                     |
+----+-----------------------+
| 1  | 4                     |
+----+-----------------------+
| 2  | 1                     |
+----+-----------------------+
| 2  | 2                     |
+----+-----------------------+
| 2  | 3                     |
+----+-----------------------+
| 3  | []                    |
+----+-----------------------+
| 4  | Select option B       |
+----+-----------------------+
| 4  | Select option C       |
+----+-----------------------+
| 4  | Select option D       |
+----+-----------------------+
| 5  | Moderate              |
+----+-----------------------+
| 5  | Heavy                 |
+----+-----------------------+
| 5  | Heavy, Big & Abnormal |
+----+-----------------------+

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,'_/',',,'),',')

How to split strings in SQL Server

SELECT substring(data, 1, CHARINDEX(',',data)-1) col1,
substring(data, CHARINDEX(',',data)+1, LEN(data)) col2
FROM table

---

Related Topics