@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?

asked 12 Feb '13, 19:09

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

edited 12 Feb '13, 20:33

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109


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
permanent link

answered 12 Feb '13, 20:32

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109
accept rate: 52%

edited 12 Feb '13, 20:32

Thats perfect. Thanks a lot all.

(13 Feb '13, 17:12) roadtrain64

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;
permanent link

answered 12 Feb '13, 20:38

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266
accept rate: 40%

edited 13 Feb '13, 08:48

1

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

(12 Feb '13, 20:40) Mark Culp
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.
permanent link

answered 14 Feb '13, 16:52

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

edited 14 Feb '13, 16:56

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836

Okay Thanks a lot.

(16 Feb '13, 11:14) roadtrain64
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×12

question asked: 12 Feb '13, 19:09

question was seen: 1,430 times

last updated: 16 Feb '13, 14:07