Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with a varchar column containing pipe-delimited values

For example:

Row 1 Column 1 = a|b|e|gg|foo 
Row 2 Column 1 = oV|foo|do 
Row 3 Column 1 = boop

How can I query this to return something like? Duplicates are ok.

Results:

column
a
b
e
gg
foo
foo
oV
do
boop

I realize this is not optimal but my hands are tied with the current schema

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

If you cannot change your current schema, then you can create a split function:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items 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(Items) values(@slice)       

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

Then you can use an outer apply to join with your table:

select x.items
from yourtable t
outer apply dbo.split(col1, '|') x;

Which will produce the following results:

| ITEMS |
---------
|     a |
|     b |
|     e |
|    gg |
|   foo |
|    oV |
|   foo |
|    do |
|  boop |

See SQL Fiddle with Demo

Or if you do not want to use a split function, then you can perform this with CTE:

;with cte (value, fullValue) as
(
  select 
      cast(left(col1, charindex('|',col1+'|')-1) as varchar(50)) value,
      stuff(col1, 1, charindex('|',col1+'|'), '') fullValue
  from yourtable
  union all
  select 
    cast(left(fullValue, charindex('|',fullValue+'|')-1) as varchar(50)) ,
    stuff(fullValue, 1, charindex('|',fullValue+',|'), '') fullValue
  from cte
  where fullValue > ''
) 
select value
from cte

See SQL Fiddle with Demo

share|improve this answer
add comment

Assuming your data contains plain alpha-numeric characters and nothing to trip up XML, you can use this query with performs some text manipulation to make your data look like XML, and then performs XQuery to slice up the column.

SELECT N.item.value('.','nvarchar(max)') item
FROM
(
     SELECT CAST('<a>'+REPLACE(Data,'|','</a><a>')+'</a>' as xml) X
       FROM Tbl T
) G
CROSS APPLY X.nodes('a') N(item)

MS SQL Server 2012 Schema Setup:

CREATE TABLE Tbl (Data varchar(max));
INSERT Tbl VALUES
  ('a|b|e|gg|foo'),
  ('oV|foo|do'), 
  ('boop');

Results (SQL Fiddle):

| ITEM |
--------
|    a |
|    b |
|    e |
|   gg |
|  foo |
|   oV |
|  foo |
|   do |
| boop |
share|improve this answer
add comment

A faster way to do this than using XML or a function is to use a tally table. The following code is an adaptation from an excellent Jeff Moden article that I most highly recommend.

create table #test
(
    Id int,
    string varchar(30)
);
insert into #test(Id, string) values
(1, 'a|b|e|gg|foo'),
(2, 'oV|foo|do'),
(3, 'boop');

declare @maxlen int;
select @maxlen = MAX(LEN(string)) from #test;
declare @delimiter char(1) = '|';

--A tally table generated from a CTE.  For small sizes (10K, at least)
--the CTE solution is markedly faster than a standard tally table.
WITH E1(N) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),                                      --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b),    --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b),    --10E+4 or 10,000 rows max
cteTally(N) AS
(
    SELECT 0 UNION ALL
    SELECT TOP (@maxlen) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(Id, N1) AS
(
    SELECT
        test.Id,
        t.N + 1
    FROM
        cteTally t
        cross join #Test test
    WHERE
        SUBSTRING(test.String, t.N, 1) = @delimiter
        OR t.N = 0
)
--      Do the actual split. The ISNULL/NULLIF combo handles the length
--      for the final element when no delimiter is found.
 SELECT test.Id,
        ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item       = SUBSTRING(test.String, s.N1, ISNULL(NULLIF(CHARINDEX(@delimiter,test.String,s.N1),0)-s.N1,8000))
   FROM cteStart s
        cross join #Test test
  WHERE s.Id = test.Id;

drop table #test;

The code is a bit more complex, but performance is significantly better, especially as the number of rows you need to parse increases.

The first several CTEs (E1, E2, and E4) are just setting up the tally table. The cteTally CTE then dynamically generates the optimal tally table length (the length of the longest string in your table). Then, cteStart finds all of the separation points. The result for your data set looks like:

+----+----+
| Id | N1 |
+----+----+
|  1 |  1 |
|  2 |  1 |
|  3 |  1 |
|  1 |  3 |
|  2 |  4 |
|  1 |  5 |
|  1 |  7 |
|  2 |  8 |
|  1 | 10 |
+----+----+

Interpreting this is a little tricky because the process adds a pseudo-delimiter (if one does not exist already) at the first position, and this is a one-indexed string. Thus, the N1 = 1 for rows 1, 2, and 3. 3 has no more delimiters. 1 has extra delimiters at positions 3, 5, 7, and 10, again noting that character "a" is actually at position 2 in this setup.

Anyhow, the final query does the actual splitting and returns your desired data set.

The tally table split is the best non-CLR solution for the string-splitting problem. If you have fewer than about 10,000 rows, the CTE tally table is faster, but after about that 10K mark, the standard tally table solution becomes better. A CLR string splitting module would be faster than both, but I know that a lot of shops are hesitant to install CLR modules.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.