0

Hello I am new to sql and I am not sure if it is possible like below

I have a row like this:

abc,xyz; bcd,ddd; qqq,eee

I want to write a query which will split the row to multiple rows like this:

abc,xyz
bcd,ddd
qqq,eee

Rows may contain 1 or 2 or 3 names or null, it is not fixed.

How can I do this?

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
Piyush Jain
  • 5
  • 6
  • Look at this one https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql Instead of the comma being the delimiter, yours would be a semi colon. Depending on the compatibility level set on your DB.. String_split may work as well – Harry Nov 29 '17 at 19:45

2 Answers2

1

Try some XML node methods which would turn your comma separated value into multiple rows 

SELECT 
      LTRIM(a.value('.', 'NVARCHAR(MAX)')) [Data] FROM
(
    SELECT CAST('<M>'+REPLACE(<column>, ';', '</M><M>')+'</M>' AS XML) AS DATA from <table_name>
) A CROSS APPLY DATA.nodes('/M') as split(a)

Result :

abc,xyz
bcd,ddd
qqq,eee
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • This is an old school way of leveraging the XML parsing in SQL. You can also "bundle" this in a string split function for general use. – Hogan Nov 30 '17 at 18:42
0
SELECT STRING_SPLIT ( fieldname, '; ')  
FROM tablename

or maybe you want a CROSS JOIN

SELECT z.field_a, z.field_b, x.VALUE
FROM tablename as z
CROSS JOIN (SELECT VALUE FROM STRING_SPLIT ( z.fieldname, '; ')) AS x

or maybe you want a CROSS APPLY

read this: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • It looks like the OP is using SQL Server 2008 or 2012, STRING_SPLIT only arrived in SQL Server 2016 as far as I know. – Alan Schofield Nov 29 '17 at 23:31
  • @AlanSchofield -- there are a million implementations of a string split function available with but a mighty web search. The xml parse by Yogesh might be faster in some cases (and many implementations use it.) – Hogan Nov 30 '17 at 18:40
  • I understand that but without stating the OP would have to create a UDF I was just clarifying the point. – Alan Schofield Nov 30 '17 at 19:47