0

(converted actaul data into example data) This is my table-

CREATE TABLE `table1` (

  `col1` int(11) NOT NULL AUTO_INCREMENT,

  `col2` text,
  )
 ENGINE=InnoDB AUTO_INCREMENT=1129311 DEFAULT CHARSET=utf8

col1 col2

1 abc,xyx,lmn

Need help in sql query to get output in below format

col1 col2

1       abc

1       xyz

1       lmn

tried below but did not worked

SELECT A.col1,

 Split.a.value(',', 'VARCHAR(10)') AS STRING

  FROM (SELECT sources, CAST ('<M>' + REPLACE(col2, ',', '</M><M>') + '</M>' AS XML) AS STRING 

  FROM table1) AS A CROSS APPLY String.nodes ('/M') AS Split(a)

  WHERE A.col1=1;
Sankar
  • 6,908
  • 2
  • 30
  • 53
Sonal
  • 43
  • 8
  • You can follow this link and its already mark as duplicate by @Drew with same link http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows. I am deleting my answer – Bharat Dec 02 '16 at 09:02
  • @BharatPatidar yeah `fthiella` had a count of 5 limit. My comment under his showed a 4k count trick here: http://stackoverflow.com/a/39696971 – Drew Dec 02 '16 at 09:08
  • Thanks for help! Below queries worked- SELECT table.col1, SUBSTRING_INDEX(SUBSTRING_INDEX(table1.col2, ',', numbers.n), ',', -1) NAME FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) numbers INNER JOIN org ON CHAR_LENGTH(table1.col2) -CHAR_LENGTH(REPLACE(table1.col2, ',', ''))>=numbers.n-1 WHERE table1.col1 IN (1) ORDER BY col1, n; – Sonal Dec 02 '16 at 10:52
  • and in oracle-------Oracle- SELECT a.col1, regexp_substr(a.col2, '[^,]+', 1, LEVEL) FROM (SELECT * FROM table1 WHERE col1 = 1) a CONNECT BY regexp_substr(a.col2, '[^,]+', 1, LEVEL) IS NOT NULL AND PRIOR a.col2 = a.col2 AND PRIOR sys_guid() IS NOT NULL; – Sonal Dec 02 '16 at 10:53

0 Answers0