Search code examples
sqlstringoracle-databasecomparison

How to ignore spaces when comprising two VARCHAR2 values

I am facing a problem with comparison, I have no idea how to force oracle to ignore spaces

I have tried trim function and replace(column,' ',''), but still

even I tried to use to_char for both two columns

this the query

select 
replace(nvl(MEMBER_CARD_NO,'x'),' ','') x,
replace(nvl(COL_CRD_ID,'x'),' ','') y,
case when 
replace(nvl(MEMBER_CARD_NO,'x'),' ','')  =  
replace(nvl(COL_CRD_ID,'x'),' ','')  then 'y' else 'no'end z
from tb_sales
where
case when 
replace(nvl(MEMBER_CARD_NO,'x'),' ','')  =  
replace(nvl(COL_CRD_ID,'x'),' ','')  then 'y' else 'no'end ='no'

the data type for each column as mentioned below

  • MEMBER_CARD_NO : VARCHAR2(30 BYTE)

  • COL_CRD_ID: VARCHAR2(30 BYTE)

below an example for one record

x       |y               |z
17140974|"17140974  "    |no

the second one shows double quotation mark when I copy it

Solution

  • it sounds like that the problem referee to ASCII and Unicode when use varchar2 its need to use char 'N' before replace like this

    select 
replace(nvl(MEMBER_CARD_NO,'x'), '\W','') x,
replace(nvl(COL_CRD_ID,'x'),'\W','') y,
case when 
replace(nvl(MEMBER_CARD_NO,'x'),'\W','')  =  
replace(nvl(COL_CRD_ID,'x'),'\W','')  then 'y' else 'no'end z
from tb_sales
where
case when 
replace(nvl(MEMBER_CARD_NO,'x'),'\W','')  =  
replace(nvl(COL_CRD_ID,'x'),'\W','')  then 'y' else 'no'end ='no'

    if it is not work use regexp_replace(coulmn, '[[:space:]]*','') instead of replace(coulmn, ' ','')