Reputation: 125
When I have string list like 1, 2, 3... I'd like to use this as one column
Ids
1
2
3
Is it possible by sql query?
ex) SELECT Ids from (1, 2, 3...)
<- I know this is not working.
Upvotes: 12
Views: 34577
Reputation: 3502
To add on @Dmitry's answer, when you have a field in a table that contains the delimited content:
SELECT
DISTINCT(jt.ids) AS id, jt.ids AS val
FROM
table_with_content r,
JSON_TABLE(
CONCAT('["', trim(REPLACE(r.delimited_field_here,';','","')) , '"]'),
"$[*]"
COLUMNS(
ids VARCHAR(1000) PATH "$"
)
) AS jt
Change the REPLACE
to find the correct delimiter. The example above looks for semicolon
.
Edit: note the distinct that will remove duplicates. This snippet was to read rows where a column was containing delimited content to return a list of unique entries.
Upvotes: -1
Reputation: 506
This works with any sanitized comma separated values.
SET @values = '1,Smith,3';
SELECT * from JSON_TABLE(CONCAT('["', REPLACE(@values, ',', '","'), '"]'),
'$[*]' COLUMNS (yourcolumn_name TEXT PATH '$')) jsontable;
Upvotes: 0
Reputation: 2162
Note, this answer is probably not compatible with the released versions of MySQL available at the time the question was asked. I added it for future references for those interested in using a recursive CTE approach. Furthermore, irregular data have been added to test the exception handling.
create table test(ids varchar(50));
insert test values('1,2,3');
-- Note: the next_start_pos is the position to the immediate right of the comma.
with recursive cte as
(select locate(',',ids,1)+1 as next_start_pos,
substring(ids,1,locate(',',ids,1)-1) as i
from test
union
select locate(',',t.ids,next_start_pos)+1 ,
case when locate(',',t.ids,next_start_pos) !=0 then substring(t.ids,next_start_pos,locate(',',t.ids,next_start_pos)-next_start_pos)
else substring(t.ids,next_start_pos) end
from test t join cte c
where next_start_pos!=1
)
select next_start_pos, i from cte where i !='';
+----------------+------+
| next_start_pos | i |
+----------------+------+
| 3 | 1 |
| 5 | 2 |
| 1 | 3 |
+----------------+------+
Note, in the main query which queries the cte result table, the where i !=''
should be included as a foolproof feature. It circumvents result of empty space caused by leading/trailing comma or consecutive comma. Take the case below for instance:
truncate test;
insert test values(',,,1,,2,3,,,,');
-- run the same query again and we get:
+----------------+------+
| next_start_pos | i |
+----------------+------+
| 6 | 1 |
| 9 | 2 |
| 11 | 3 |
+----------------+------+
-- if the WHERE clause is removed, then we get:
+----------------+------+
| next_start_pos | i |
+----------------+------+
| 2 | |
| 3 | |
| 4 | |
| 6 | 1 |
| 7 | |
| 9 | 2 |
| 11 | 3 |
| 12 | |
| 13 | |
| 14 | |
| 1 | |
+----------------+------+
Upvotes: 0
Reputation: 1
You can use below stored procedure to split string delimted by any character:
CREATE PROCEDURE `split_delimited` (
IN inputstr NVARCHAR(1000),
IN delimiter CHAR(1)
)
BEGIN
DROP TEMPORARY TABLE Items;
CREATE TEMPORARY TABLE Items(item NVARCHAR(50));
WHILE LOCATE(delimiter,inputstr) > 1 DO
INSERT INTO Items SELECT SUBSTRING_INDEX(inputstr,delimiter,1);
SET inputstr = REPLACE (inputstr, (SELECT LEFT(inputstr,LOCATE(delimiter,inputstr))),'');
END WHILE;
INSERT INTO Items(item) VALUES(inputstr);
select * from Items;
END
Input: 'a,b,c' Output: a b c
Upvotes: 0
Reputation: 7276
For MySQL 8.0.4+
SELECT *
FROM
JSON_TABLE(
CONCAT('[', '1,2,3,4', ']'),
"$[*]"
COLUMNS(
ids BIGINT(20) PATH "$"
)
) AS tt
Concatenate square brackets ([]
) around your string to make it into a JSON array. Then use JSON_TABLE
to convert it into a table. See the MySQL JSON Table Functions for more info.
Upvotes: 14
Reputation: 26804
Use a subquery of arbitrary digits to split your string.Instead of vals you can use '1,2,3'.
SELECT
DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val
FROM
tt1
INNER JOIN
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;
Upvotes: 13