TRIM BOTH Teradata not working for single quotes

Database
Sir
Enthusiast

TRIM BOTH Teradata not working for single quotes

    SELECT TRIM(BOTH 'a' FROM 'aaaaaaaaaaaaasdfasa');

 

works fine and returns sdfas
but I am trying to remove quotes, so I did

  

   SELECT TRIM(BOTH ''' FROM (''2565','5216','5364'') ;

 

I get error - Query ends within a string or comment block. Please suggest how to do this

3 REPLIES
Teradata Employee

Re: TRIM BOTH Teradata not working for single quotes

First, a single-quote within single-quotes is escaped by doubling it, so you quote a quote as ''''.  Therefore you need to type 4 single-quotes instead of 3 after BOTH.  Second, note that TRIM() only works on leading and trailing characters.  To remove all occurrences of a character use OPREPLACE() or REGEXP_REPLACE().

Sir
Enthusiast

Re: TRIM BOTH Teradata not working for single quotes

Hi GJColeman,

4 quotes didn't work either. I get :

 Error 3706. Syntax error: expected something between a string or a unicode character literal and the integer '2565'.

What would be the correct syntax for OPREPLACE() or REGEXP_REPLACE()?

I tried

WHERE Group_ID  IN OReplace(''2565','5216','5364'', '[']', ' ')

error [3707] Syntax error, expected something like ',' between a string or a Unicode character literal and the integer '2565'.

WHERE Group_ID  IN REGEXP_REPLACE(''2565','5216','5364'', '[']', '',1,0,i) also errored

Highlighted
Teradata Employee

Re: TRIM BOTH Teradata not working for single quotes

Here is what I mean by doubling the single-quotes:

 

oreplace('''2565'',''5216'',''5364''','''','')
returns:
2565,5216,5364

 

You cannot say "select ... where <something> in oreplace(...)".  Oreplace does not return an in-list.  If you are getting quoted numbers as a parameter in a stored procedure then you can remove them and use that variable in an SQL statement.

 

Create procedure ... IN(...,in_list varchar(32),...) ...

Declare v_in_list varchar(32);

...

set v_in_list = oreplace(in_list,'''','');

call dbc.sysexecsql('select ... where <something> in (' || :v_in_list || ')';

...