CODEWITHSUNDEEP
sqlsql-serverfunctionsplit
SQL_Noob
SQL_Noob

Reputation: 1291

Reverse Concat - Split function

I have a table and it has a value column that lists data as: Row 1: '00','01','02','03' Row 2: '03','02','09','08'

I have a couple of split functions

     FUNCTION [dbo].[udf_Split](@String varchar(MAX), @Delimiter char(1))   
    returns @temptable TABLE (Item varchar(MAX))       
    as       
    begin      
      declare @idx int       
      declare @slice varchar(8000)       

select @idx = 1       
    if len(@String)<1 or @String is null  return       

while @idx<>0       
begin       
    set @idx = charindex(@Delimiter,@String)       
    if @idx!=0       
        set @slice = left(@String,@idx - 1)       
    else       
        set @slice = @String       

    if(len(@slice)>0)  
        insert into @temptable(Item) values(@slice)       

    set @String = right(@String,len(@String) - @idx)       
    if len(@String) = 0 break       
end  return end;

I'm trying to create a view of the table, with that column and then I'd like my view results to be a list of rows that have each value broken to its own row (and distinct) So would look like: (the tics can stay or go, don't care about them right now) Row 1: 00 Row 2: 01 Row 3: 02 Row 4: 03

My view is pretty much a: 

    SELECT DISTINCT VALUE FROM TABLE
    cross apply dbo.split(Value, ',') as Item

But it's not working. Can someone lend me some direction on how I should work this?

Upvotes: 2

Views: 7543

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

This is because you're SELECTing the field VALUE instead of Item.Item. You should do this:

SELECT DISTINCT x.Item
FROM TABLE
CROSS APPLY dbo.split(Value, ',') AS x

Additionally, your dbo.split function is not optimal. There are number of ways to split a string in a set-based fashion, instead of RBAR. Here is one way using XML:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = 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)
   );
GO

Sample usage:

;WITH CteData(Value) AS(
    SELECT '''00'',''01'',''02'',''03''' UNION ALL
    SELECT '''03'',''02'',''09'',''08'''
)
SELECT DISTINCT x.Item
FROM CteData d
CROSS APPLY dbo.SplitStrings_XML(d.Value, ',') x

Result:

Item
--------
'00'
'01'
'02'
'03'
'08'
'09'

For other string splitter, you can read this article by Aaron Bertrand.

Upvotes: 3

Related Questions