Query to split String value into multiple rows

Question

i have

id | dvr
1  | 1,2,3
2  | 1,3,4
3  | 1,5,6,7,8

and would like to have

id | dvr
1  | 1
1  | 2
1  | 3
2  | 1
2  | 3
2  | 4
... and so on

what is the fastest query i should use?

What's your DBMS? Anyway there are tons of answers about this. — dario
– dario, Commented Jan 28, 2015 at 9:31
It very much depends on what database you are using. Please update your question with this vital information. — jpw
– jpw, Commented Jan 28, 2015 at 9:32
Another thing, which we greatly value before answering any questions is to show us what was your attempt at writing a query and find a solution yourself. — Radu Gheorghiu
– Radu Gheorghiu, Commented Jan 28, 2015 at 9:35

Disha · Accepted Answer · 2015-01-28 10:04:02Z

1

Make a sql function as below:

create Function [dbo].[fun_CSVToTable] 
(
    @LIST varchar(7000),
    @Delimeter varchar(10)
)
RETURNS @RET1 TABLE (RESULT BIGINT)
AS
BEGIN
    DECLARE @RET TABLE(RESULT BIGINT)

    IF LTRIM(RTRIM(@LIST))='' RETURN  

    DECLARE @START BIGINT
    DECLARE @LASTSTART BIGINT
    SET @LASTSTART=0
    SET @START=CHARINDEX(@Delimeter,@LIST,0)

    IF @START=0
    INSERT INTO @RET VALUES(SUBSTRING(@LIST,0,LEN(@LIST)+1))

    WHILE(@START >0)
    BEGIN
        INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,@START-@LASTSTART))
        SET @LASTSTART=@START+1
        SET @START=CHARINDEX(@Delimeter,@LIST,@START+1)
        IF(@START=0)
        INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,LEN(@LIST)+1))
    END

    INSERT INTO @RET1 SELECT * FROM @RET
    RETURN 
END

answered Jan 28, 2015 at 10:04

Disha

3922 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

UlfR · Accepted Answer · 2015-01-28 09:41:59Z

0

If you are running postgresql and dvr column is text you could do:

select
  id,
  unnest(string_to_array(dvr,','))
from your_table;

answered Jan 28, 2015 at 9:41

UlfR

4,4452 gold badges37 silver badges51 bronze badges

1 Comment

Alessio Bacin Over a year ago

i'm using MS sql server

Collectives™ on Stack Overflow

Query to split String value into multiple rows

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related