Special Character Removal

Database
Highlighted
Enthusiast

Special Character Removal

Hi Experts,

 

I am getting a spool apce issue while i use the below function for replacing special characters:

 

SyntaxEditor Code Snippet

REGEXP_REPLACE(RU.MARKET_NAME, '[^0-9a-z]', '_', 1, 0, 'i')) 

The same i tried with otranslate, but it is not getting the desired result where 

SyntaxEditor Code Snippet

oTranslate(MARKET_NAME,' /-','_')

it is not replacing the / character.

 

Any help much appreciated.

 

Thanks

Sunny

 

2 REPLIES
Teradata Employee

Re: Special Character Removal

Hi Sunny,

 

The OTRANSLATE function replace characters one by one according to their matching position. When there no character in regard to the position, it is removed.

Check with this syntax :

with cte_data (str) as (select 'abc def-ghi/jkl')
select str
     , otranslate(str, ' /-', '_')   as tr1
     , otranslate(str, ' /-', '___') as tr2
  from cte_data;

str              tr1            tr2
---------------  -------------  ---------------
abc def-ghi/jkl  abc_defghijkl  abc_def_ghi_jkl

 

Junior Contributor

Re: Special Character Removal

You probably use this calculation in ORDER/GROUP/PARTITION BY where a VarChar is expanded to the defined size (like CHAR(xx)).

REGEXP_REPLACE returns a VarChar(8000).

 

Simply add a CAST(... AS VARCHAR(??)) which matches the actual data type of the source column.