How to remove quotes in sybase ASE

Question

I have a string:

"16680,16678,16677,16676,16675,16672"

Which I got from the Stored procedure passed parameter while calling it. I want to insert these records in where in like

Where in (16680,16678,16677,16676,16675,16672).

How can I make this in Sybase ASE stored procedure?

Answer 1

You can use the str_replace() function to replace the double quotes with NULL, eg:

declare @strings varchar(15)

select  @strings = '"1,2,3,4,5"'

select  @strings, str_replace(@strings,'"',NULL)
go

 --------------- ---------------
 "1,2,3,4,5"     1,2,3,4,5

Feeding the new string into a query requires a bit more work though.

Trying to feed directly into a query generates an error, eg:

declare @strings varchar(15)

select  @strings = '"1,2,3,4,5"'

select id from sysobjects where id in ( str_replace(@strings,'"',NULL) )
go

Msg 257, Level 16, State 1:
Server 'ASE400', Line 7:
Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the CONVERT function to run this query.

To get around this issue we can dynamically build the query and then run it via an execute() call, eg:

declare @strings varchar(15),
        @query   varchar(100)

select  @strings = '"1,2,3,4,5"'

select  @query = 'select id from sysobjects where id in (' || str_replace(@strings,'"',NULL) || ')'

print   @query

execute (@query)
go

select id from sysobjects where id in (1,2,3,4,5)

 id
 -----------
           1
           2
           3
           4
           5

Another solution that does away with the @query variable:

declare @strings varchar(15)

select  @strings = '"1,2,3,4,5"'
select  @strings = str_replace(@strings,'"',NULL)

execute( 'select id from sysobjects where id in (' || @strings || ')' )
go

 id
 -----------
           1
           2
           3
           4
           5

NOTE: all code was run in an ASE 16.0 SP04 GA instance.