Split String Column values

Question

acctcode         primekey          groupby    <--- columns

 WDS                1              'NULL'     <--- values (varchar)       
 FDS                2              'NULL'
 IRN                3              'NULL'
 SUM                4              1,2,3
 STL                5              'NULL'
 WTR                6              'NULL'
 SUM2               7               5,6

I want to split string groupby column where values are NOT EQUAL to 'NULL' and save it to another table which will look like this:

acctcode          primekey         groupby
 SUM                 4                1
 SUM                 4                2
 SUM                 4                3
 SUM2                7                5
 SUM2                7                6

here is my split string code:

    ALTER FUNCTION [dbo].[SplitStrings]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT primekey = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

here is the code I use to call the function:

SELECT acctcode,(SELECT * FROM SplitStrings(groupby,','))as prime 
INTO Chadtblsum
FROM Chadothercharges WHERE acctcode = acctcode

The code above gives me this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Felix Pamittan · Accepted Answer · 2015-08-05 02:41:50Z

4

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

The above error happens since your subquery in the SELECT returns more than one row. Try executing this:

SELECT * FROM SplitStrings('1,2,3',',') x

You'll see that it'll return 3 rows, one for each item.

In order to fix this, you have to use CROSS APPLY:

SQL Fiddle

SELECT 
    c.acctcode,
    x.primekey AS prime
INTO Chadtblsum
FROM Chadothercharges c
CROSS APPLY SplitStrings(c.groupby,',') x
WHERE groupby <> 'NULL'

edited Aug 5, 2015 at 2:41

answered Aug 5, 2015 at 2:35

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chad Patrick Over a year ago

Thank you @Felix Pamittan - I used your code and adjust it a little bit to get the specific data i needed:

SELECT      c.acctcode,     x.primekey AS prime INTO Chadtblsum FROM Chadothercharges c CROSS APPLY SplitStrings_XML(c.groupby,',') x WHERE acctcode = acctcode AND groupby <> 'NULL' ORDER BY primekey

Felix Pamittan Over a year ago

No problem. Glad to have been of help.

mohan111 · Accepted Answer · 2015-08-05 02:40:00Z

0

declare  @Table1 TABLE 
    ([acctcode] varchar(4), [primekey] int, [groupby] varchar(8))
;

INSERT INTO @Table1
    ([acctcode], [primekey], [groupby])
VALUES
    ('WDS', 1, NULL),
    ('FDS', 2, NULL),
    ('IRN', 3, NULL),
    ('SUM', 4, '1,2,3'),
    ('STL', 5, NULL),
    ('WTR', 6, NULL),
    ('SUM2', 7, '5,6')
;

select   [acctcode], [primekey],
         Split.a.value('.', 'VARCHAR(100)') AS SubColour  
     FROM  (SELECT [acctcode], [primekey],
             CAST ('<M>' + REPLACE([groupby], ',', '</M><M>') + '</M>' AS XML) AS String  
         FROM  @Table1) AS A CROSS APPLY String.nodes ('/M') AS Split(a)

answered Aug 5, 2015 at 2:40

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Collectives™ on Stack Overflow

Split String Column values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related