How convert string to array on SQL

Question

In a column I have "1;2;3;6-9"

I need make this string in a array like this (1,2,3,6,7,8,9)

select range from my_table

return

| range     |
|-----------| 
| 1;2;3;6-9 |

I need run

select id from my_another_table where id in("1;2;3;6-9")

| id |
|----| 
| 1  |
| 2  |
| 3  |
| 6  |
| 7  |
| 8  |
| 9  |

Do you have to do this in SQL? Can you do it in your application code instead? — Dai, Feb 08 '19 at 23:04

score 3 · Accepted Answer · answered Feb 08 '19 at 23:09

This is a lousy structure for data. But you can do this with generate_series() and string functions:

select generate_series(v2.lo, v2.hi, 1)
from (values ('1;2;3;6-9')) v(str) cross join lateral
     regexp_split_to_table(v.str, ';') as r(range) cross join lateral
     (values (case when range not like '%-%' then range::int else split_part(range, '-', 1)::int end,
              case when range not like '%-%' then range::int else split_part(range, '-', 2)::int end
             )
     ) v2(lo, hi);

score 1 · Answer 2 · answered Feb 08 '19 at 23:59

Just in case that you are still interested, you can create a function.

create or replace function parse_ranges(value TEXT) returns setof int as
$func$
DECLARE
    range RECORD;
    ra int;
    rb int;
BEGIN
    FOR range IN SELECT * FROM regexp_split_to_table(value, ';') as r(r) LOOP
        IF range.r LIKE '%-%' THEN
            ra = split_part(range.r, '-', 1)::int;
            rb = split_part(range.r, '-', 2)::int;
            RETURN QUERY SELECT * FROM generate_series(ra, rb, 1);
        ELSE
            RETURN NEXT range.r::int;
        END IF;
    END LOOP;
END;
$func$
LANGUAGE plpgsql;

Then you can use it:

select id from my_another_table where id in(SELECT parse_ranges('1;2;3;6-9'))

score 0 · Answer 3 · answered Oct 17 '22 at 09:47

    SELECT *
      FROM [dbo].[Table_1] 
     where Id in (select [value] from openjson((select conf.[Value] FROM Table_2 conf WHERE conf.[Key]='Range'), '$'))

Where

Table_2 "data must be JSon" range (Nvarchar) =>'[1,2,3,4]'

Table_1 ID Int => 1,2,4,5

How convert string to array on SQL

3 Answers3