How to Split a Varchar Column as Multiple Values in SQL

How to split a varchar column as multiple values in SQL?

Wrap the value in the delimiter you are using in the delimited list and then check if it is a sub-string of the delimited list (also with the delimiters wrapped around it):

SELECT r.Value
FROM   AD_Ref_List r
       INNER JOIN xx_insert x
       ON ( ',' || x.XX_DocAction_Next || ',' LIKE '%,' || r.value || ',%' )
WHERE  r.AD_Reference_ID = 1000448
AND    x.xx_insert_id    = 1000283;

i must keep the logic in the whereClause

Really, don't. The above query will be much more efficient.

But if you have to then:

SELECT Value
FROM   AD_Ref_List
WHERE  AD_Reference_ID = 1000448
AND    value IN (
  SELECT REGEXP_SUBSTR( XX_DocAction_Next, '[^,]+', 1, LEVEL )
  FROM   xx_insert
  WHERE  xx_insert_id    = 1000283
  CONNECT BY LEVEL <= REGEXP_COUNT( XX_DocAction_Next, '[^,]+' )
);

Split multiple values from a string in one column, into multiple columns using SQL Server

With a bit of JSON and assuming you have a known or maximum number of tags

Select A.CompanyName
      ,A.CompanyNumber
      ,Tag1  = JSON_VALUE(S,'$[0]')
      ,Tag2  = JSON_VALUE(S,'$[1]')
      ,Tag3  = JSON_VALUE(S,'$[2]')
From  YourTable A
Cross Apply ( values ( '["'+replace(STRING_ESCAPE(Tags,'json'),';','","')+'"]' ) ) B(S)

SQL - Splitting string in multiple columns

Here is one way to do it using JSON functions:

select t.name,
    json_value(x.obj, '$[0]') name1,
    json_value(x.obj, '$[1]') name2,
    json_value(x.obj, '$[2]') name2,
    json_value(x.obj, '$[3]') name4
from mytable t
cross apply (values('["' + replace(t.name, '_', '", "') + '"]')) x(obj)

The trick is to manipulate the string to make it look like a JSON array (that' what the cross apply subquery does). Basically this turns a string like 'A_B_C' to '["A", "B", "C"]'. We can then use json_value() to easily access each individual element.

This does not assume anything about the elements being unique. Actually the only requirement is that the string should not contain embedded double quotes.

Demo on DB Fiddle:

name           | name1 | name2 | name2 | name4
:------------- | :---- | :---- | :---- | :----
ABC_DEFG_HIJKL | ABC   | DEFG  | HIJKL | null 
A_B_C          | A     | B     | C     | null 
A_B_C_D        | A     | B     | C     | D    

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

Split string into multiple columns TSQL

change query to :

;WITH cte (PK, product,standardcost,currentcost,variance,stages)
AS
(
SELECT 
    [PK],
    [product],
    [standardcost],
    [currentcost],
    [variance],
    CONVERT(XML,'<Product><Attribute>' 
        + REPLACE([stages],',', '</Attribute><Attribute>') 
        + '</Attribute></Product>') AS Prod_Attributes
FROM @tempcostings2021
)
SELECT 
    [PK],
    [product],
    [standardcost],
    [currentcost],
    [variance],
    stages.query('/Product/Attribute[1]').value('/', 'varchar(max)') AS [Stage1],
    stages.query('/Product/Attribute[2]').value('/', 'varchar(max)') AS [Stage2],
    stages.query('/Product/Attribute[3]').value('/', 'varchar(max)') AS [Stage3],
    stages.query('/Product/Attribute[4]').value('/', 'varchar(max)') AS [Stage4]
FROM cte

   

---

Related Topics