Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216'

and I want to use it to search IN this field:

SELECT * 
FROM table1
WHERE id IN (id_list)
  • id is integer

  • id_list is varchar/text

But in this way this doesn't work, so I need in some way to split id_list into select query.

What solution should I use here? I'm using the T-SQL Sybase ASA 9 database (SQL Anywhere). But in this way this doesn't work, so I need in some way to split id_list into select query.

Way I see this, is to create own function with while loop through, and each element extract based on split by delimiter position search, then insert elements into temp table which function will return as result.


COMMENTS TO Sebastian Meine ANSWER AND HIS SOLUTIONS :

Sybase SQL Anywhere 9

  • sa_split_list system procedure not exist here, so it doesn't work

  • CAST works great

Sybase SQL Anywhere 12

  • sa_split_list system procedure exist and works great

  • CAST works great

For the Sybase SQL Anywhere 9 I have made sa_split_list system procedure replacement:

CREATE PROCEDURE str_split_list(in str long varchar, in delim char(10) default ',')
RESULT(
  line_num integer,
  row_value long varchar)
BEGIN
  DECLARE str2 long varchar;
  DECLARE position integer;

   CREATE TABLE #str_split_list (
   line_num integer DEFAULT AUTOINCREMENT,
   row_value long varchar null,
   primary key(line_num));

   SET str = TRIM(str)+delim;
   SET position = CHARINDEX(delim, str);

   separaterows:
   WHILE position > 0 loop
       SET str2 = TRIM(LEFT(str, position - 1));
       INSERT INTO #str_split_list (row_value)
       VALUES (str2);
       SET str = RIGHT(str, LENGTH(str) - position);
       SET position = CHARINDEX(delim, str);
    end loop separaterows;

   select * from #str_split_list order by line_num asc;

END

Execute the same way as sa_split_list:

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216')
share|improve this question
1  
The problem you are having is because your design is wrong. Don't store comma delimited lists in a database. This violates first normal form. You need a junction table with a row for each table1_id, other_id combination. –  Martin Smith Oct 12 '13 at 13:14
    
@JonSeigel - Does Sybase have TVFs and is the syntax the same? They were added to the product after the code base split AFAIK. –  Martin Smith Oct 12 '13 at 15:07
    
@Martin: Oops. Didn't notice the [sybase] tag. I have no idea; my close vote may be inappropriate. Maybe some of the techniques presented in there will apply? –  Jon Seigel Oct 12 '13 at 15:11
    
@JonSeigel - A google indicates that there is something built in SELECT row_value as testRecord FROM sa_split_list('A,B,C'); but I have no idea if Sybase has CROSS APPLY or something to use this against a column. –  Martin Smith Oct 12 '13 at 15:19

1 Answer 1

up vote 7 down vote accepted

I don't actually have a sybase system to test with, but according to http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html the following solution should work:

SELECT * 
  FROM table AS T
  JOIN sa_split_list('1234,23,56,576,1231,567,122,87876,57553,1216') AS L
    ON T.id = L.row_value;

If the id_list is actually stored in another table (say list_table) you need to also use a CROSS APPLY operator (http://dcx.sybase.com/1101/en/dbusage_en11/apply-joins-joinsasp.html):

SELECT *
  FROM list_table AS LT
 CROSS APPLY sa_split_list(LT.id_list) L
  JOIN table T
    ON T.id = L.row_value
   AND <optional: some other condition that ties rows in list_table to rows in table, like a group membership or location>

This all requires that only numbers are part of these list as the split pieces will be automatically cast to the datatype of the id column. If that is not the case you can use this simpler, but slower technique:

SELECT *
  FROM table
 WHERE ','+@id_list+',' LIKE '%,'+CAST(id AS VARCHAR(20))+',%'

That last example assumed that the id list is in a variable @id_list. if it is in another table you can use the same CROSS APPLY technique from above. The additional commata make sure that the first or last entry can be matched.

share|improve this answer
    
Thank you, this is what I have been looking for. –  BlueMark Dec 26 '13 at 21:49
    
I was looking for something like this and again stackexchange saved me :) Thank You for detailed answer and links! –  Misiu Feb 18 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.