CODEWITHSUNDEEP
sqlsql-servert-sql
macemers
macemers

Reputation: 2222

How to split the string value in one column and return the result table

Assume we have the following table:

id name   member
1  jacky  a;b;c
2  jason  e
3  kate   i;j;k
4  alex   null

Now I want to use the sql or t-sql to return the following table:

1 jacky a
1 jacky b
1 jacky c
2 jason e
3 kate  i
......

How to do that? I'm using the MSSQL, MYSQL and Oracle database.

Upvotes: 2

Views: 5980

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

This is the shortest and readable string-to-rows splitter one could devise, and could be faster too.

Use case of choosing pure CTE instead of function, e.g. when you're not allowed to create a function on database :-)

Creating rows generator via function(which could be implemented by using loop or via CTE too) shall still need to use lateral joins(DB2 and Sybase have this functionality, using LATERAL keyword; In SQL Server, this is similar to CROSS APPLY and OUTER APPLY) to ultimately join the splitted rows generated by a function to the main table.

Pure CTE approach could be faster than function approach. The speed metrics lies in profiling though, just check the execution plan of this compared to other solutions if this is indeed faster:

with Pieces(theId, pn, start, stop) AS
(
      SELECT id, 1, 1, charindex(';', member)
      from tbl

      UNION ALL

      SELECT id, pn + 1, stop + 1, charindex(';', member, stop + 1)
      from tbl 
      join pieces on pieces.theId = tbl.id 
      WHERE stop > 0
)
select 

      t.id, t.name, 

      word = 
         substring(t.member, p.start,             
           case WHEN stop > 0 THEN p.stop - p.start 
           ELSE 512 
           END) 

from tbl t
join pieces p on p.theId = t.id
order by t.id, p.pn

Output:

ID  NAME    WORD
1   jacky   a
1   jacky   b
1   jacky   c
2   jason   e
3   kate    i
3   kate    j
3   kate    k
4   alex    (null)

Base logic sourced here: T-SQL: Opposite to string concatenation - how to split string into multiple records

Live test: http://www.sqlfiddle.com/#!3/2355d/1

Upvotes: 3

Tom
Tom

Reputation: 91

IF OBJECT_ID('dbo.Users') IS NOT NULL 
    DROP TABLE dbo.Users;

CREATE TABLE dbo.Users
(
  id INT IDENTITY NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  member VARCHAR(1000)
)
GO

INSERT INTO dbo.Users(name, member) VALUES
  ('jacky', 'a;b;c'),
  ('jason', 'e'),
  ('kate', 'i;j;k'),
  ('alex', NULL);
GO

DECLARE @spliter CHAR(1) = ';';
WITH Base AS
(
    SELECT  1 AS n
    UNION ALL
    SELECT  n + 1
    FROM    Base
    WHERE   n < CEILING(SQRT(1000)) --generate numbers from 1 to 1000, you may change it to a larger value depending on the member column's length.
)
, Nums AS --Numbers Common Table Expression, if your database version doesn't support it, just create a physical table.
(
    SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT  0)) AS n
    FROM    Base AS B1 CROSS JOIN Base AS B2
)
SELECT  id,
        SUBSTRING(member, n, CHARINDEX(@spliter, member + @spliter, n) - n) AS element
FROM    dbo.Users
    JOIN Nums
    ON n <= DATALENGTH(member) + 1
    AND SUBSTRING(@spliter + member, n, 1) = @spliter
ORDER BY id
OPTION (MAXRECURSION 0); --Nums CTE is generated recursively, we don't want to limit recursion count.

Upvotes: 1

Chris Gessler
Chris Gessler

Reputation: 23113

Well... let me first introduce you to Adam Machanic who taught me about a Numbers table. He's also written a very fast split function using this Numbers table.

http://dataeducation.com/counting-occurrences-of-a-substring-within-a-string/

After you implement a Split function that returns a table, you can then join against it and get the results you want.

Upvotes: 2

Related Questions