Question Re: insert comma seperated values in table in Technology Q&A

insert comma seperated values in table

Former Member — Tue, 12 Feb 2013 19:09:40 GMT

@id = 1,2,3,4,5
@name = 'NY, NJ, VA, DC,CA'

I have a temp table called #tmp (id int, name varchar(4)). I want to insert these above comma delimited values in this temp table, so 1 has corresponding value = NY, 2 has NJ etc...so total 5 rows will be inserted. What is the best way to code that in SQL Anywhere?

Re: insert comma seperated values in table

graeme_perrow — Tue, 12 Feb 2013 20:32:01 GMT

You want to use the sa_split_list function to break the strings into a result set. Since there are two of them, you'll need to join them. This seems to work:

insert into #tmp (id,name) select ID.row_value id, VAL.row_value name 
    from sa_split_list(@name) VAL join sa_split_list(@id) ID on ID.line_num=VAL.line_num

Re: insert comma seperated values in table

MarkCulp — Tue, 12 Feb 2013 20:38:41 GMT

Try something like this:

insert into #tmp( id, name )
select a.row_value, b.row_value
  from sa_split_list( @id ) a
  join sa_split_list( @name ) b
    on a.line_num = b.line_num;

Note that I have not tested this. You can read more information about sa_split_list in the docs.

I noticed that you have some embedded spaces in your comma-delimited strings. If you don't want those included in your tmp table then you will can use trim to remove any that exists. Example:

insert into #tmp( id, name )
select trim( a.row_value ), trim( b.row_value )
  from sa_split_list( @id ) a
  join sa_split_list( @name ) b
    on a.line_num = b.line_num;

Re: insert comma seperated values in table

MarkCulp — Tue, 12 Feb 2013 20:40:06 GMT

Looks like Graeme hit enter a few minutes before I did 🙂 ... I took too long to look up the references in the docs!

Re: insert comma seperated values in table

Former Member — Wed, 13 Feb 2013 17:12:58 GMT

Thats perfect. Thanks a lot all.

Re: insert comma seperated values in table

Former Member — Thu, 14 Feb 2013 16:52:28 GMT

CREATE TABLE #tmp_tbl
          ( qualifier_state_id    integer
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          )
INSERT INTO #tmp_tbl(qualifier_state_name) VALUES ('SAMLL')
INSERT INTO #tmp_tbl(qualifier_state_name) VALUES ('YELLO')

Thanks but I have qualifier_state_name in my temp table and my qualifier_state_ids are coming from variavle @qualifier_state_ids = '58,61' Where 58= SMALL and 61= Yello

Now I need to update below temp table and make qualifier_state_id =58 for small and 61 for Yello I tried below but not workin

UPDATE #tmp_tbl                             a
                    , sa_split_list(@lQualifierStateIds)   b
                  SET a.qualifier_state_id   = TRIM(b.row_value)-- this updates both state_ids =61 whichh is wrong.

Re: insert comma seperated values in table

Former Member — Sat, 16 Feb 2013 11:14:10 GMT

Okay Thanks a lot.