Search code examples
sqlsybasesap-ase

SYBASE ASE String replace in WHERE clause

I have a SYBASE ASE table with below values :

Table 1 :

**Value**     **Status**
A          STATUS 1
B          STATUS 3
C          STATUS 4

I have to filter the values based on the list of values like this .. STATUS1,STATUS2,STATUS3 (no space between values).

I want to remove the space/blanks from the value column from Table 1 and compare against the list.

I tried the below code and it wasn't working

select value ,status from  Table 1
where str_replace(status,' ','') IN ('STATUS1','STATUS2','STATUS3')

select value ,status  from  Table 1
where str_replace(status,' ',NULL) IN ('STATUS1','STATUS2','STATUS3')

Any idea how to achieve without changing the list values

Solution

  • The latter one should work (apart from the Table 1 table name).

    Note that an empty string in Sybase is often interpreted as a single space. See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1550/html/blocks/blocks311.htm