I just want to split a string as column and rows in MS SQL
Example:
String : 26412|7|554.40#42470|10|1504.40#49606|10|274.40#378982|10|425.84
Delimiter : | as column,
Delimiter : # as rows
help to find solution Thanks in advance.
Expected Output:
Col1 Col2 Col3
-----------------------
26412 07 554.40
42470 10 1504.40
49606 10 274.40
378982 10 425.84
This could be done with the help of XML:
DECLARE @str nvarchar(max) = N'26412|7|554.40#42470|10|1504.40#49606|10|274.40#378982|10|425.84',
@xml xml
SELECT @xml = CAST('<s><b>'+REPLACE(REPLACE(@str,'#','</b></s><s><b>'),'|','</b><b>')+'</b></s>' as xml)
SELECT t.v.value('b[1]','int') as Col1,
t.v.value('b[2]','int') as Col2,
t.v.value('b[3]','float') as Col3
FROM @xml.nodes('/s') as t(v)
Output:
Col1 Col2 Col3
26412 7 554,4
42470 10 1504,4
49606 10 274,4
378982 10 425,84
Using one of the split strings from here:
declare @str varchar(max)
set @str='26412|7|554.40#42470|10|1504.40#49606|10|274.40#378982|10|425.84'
;with cte(mainitem)
as
(
select * from [dbo].[SplitStrings_Numbers](@str,'#') b
)
,cte1 as
(select *,row_number() over (partition by mainitem order by mainitem) as rn from cte c
cross apply
[dbo].[SplitStrings_Numbers](c.mainitem,'|') )
select
max(case when rn=1 then cast(item as varchar(100)) end) as col1,
max(case when rn=2 then cast(item as varchar(100))end) as col2,
max(case when rn=3 then cast(item as varchar(100)) end) as col3
from cte1
group by mainitem
Output:
26412 7 554.40
378982 10 425.84
42470 10 1504.40
49606 10 274.40
Try like this,
DECLARE @List NVARCHAR(max) = '26412|7|554.40#42470|10|1504.40#49606|10|274.40#378982|10|425.84'
SELECT col1 AS ActualData
,PARSENAME(REPLACE(col1, '|', '.'), 4) col1
,PARSENAME(REPLACE(col1, '|', '.'), 3) col2
,substring(col1, (LEN(col1) - CHARINDEX('|', REVERSE(col1))) + 2, len(col1)) AS Col3
FROM (
SELECT y.i.value('(./text())[1]', 'nvarchar(4000)') AS col1
FROM (
SELECT x = CONVERT(XML, '<i>' + REPLACE(@List, '#', '</i><i>') + '</i>').query('.')
) AS a
CROSS APPLY x.nodes('i') AS y(i)
) T
This is working very well for me in MySql 5.5
CREATE TABLE split ( id INT, str VARCHAR(50) );
INSERT INTO split VALUES (1, 'Smith'), (2, 'Julio|Jones|Falcons'), (3,
'White|Snow'), (4, 'Paint|It|Red'), (5, 'Green|Lantern'), (6, 'Brown|bag');
DELIMITER |
CREATE PROCEDURE SplitColumnsToRows ()
BEGIN
DECLARE id1 INT (100);
DECLARE str1 VARCHAR (100);
DECLARE done INTEGER DEFAULT 0;
DECLARE tokens INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE token VARCHAR (100);
DECLARE cur CURSOR FOR
SELECT
id,
str
FROM
split;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
parse :
LOOP
FETCH cur INTO id1,
str1;
IF done = 1
THEN LEAVE parse;
END IF;
SET tokens =
(SELECT
LENGTH(str1) - LENGTH(REPLACE(str1, '|', '')) + 1);
DELETE
FROM
split
WHERE id = id1
AND str = str1;
SET i = 1;
WHILE
i <= tokens DO IF i = tokens
THEN SET token = str1;
ELSE SET token = SUBSTR(str1, 1, INSTR(str1, '|') - 1);
SET str1 = SUBSTR(str1, INSTR(str1, '|') + 1, LENGTH(str1));
END IF;
INSERT INTO split
VALUES
(id1, token);
SET i = i + 1;
END WHILE;
END LOOP parse;
CLOSE cur;
END |
DELIMITER;
