Teradata remove single quotes from variable

Database
Sir
Enthusiast

Teradata remove single quotes from variable

I need to replace single quotes in a string of numbers and use in a WHERE IN clause. for example, I have

WHERE Group_ID IN (''4532','3422','1289'')

The criteria within parenthesis is being passed as a parameter, so I have no control over that. I tried using :

WHERE Group_ID IN (REGEXP_REPLACE(''4532','3422','1289'', '[']', ' ',1,0,i))

also tried using OReplace

WHERE Group_ID IN (OReplace(''4532','3422','1289'', '[']', ' '))

but get the same error:

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

Please suggest how to remove the single enclosing quotes or even removing all single quotes should work as well.

4 REPLIES
Teradata Employee

Re: Teradata remove single quotes from variable

Is this a parameter of a stored procedure or a macro?  If the query using where-in is in a SP, then you can edit the string with oreplace() much as you have coded it.  But to test it using a hard-coded literal as you show here means changing the quotes:  the outer single-quotes should not be doubled, and the inner single-quotes should be doubled.

Enthusiast

Re: Teradata remove single quotes from variable

Hi Sir
I have got similar problem
I wanna remove single quote from a address line.
Orepalce isn't working as it is not reading the single qoute correctly.

Select oreplace(address,"'",'') is not working
Can you pls suggest some.alternative how could we reaolve thia
Tags (1)
Teradata Employee

Re: Teradata remove single quotes from variable

You can't use the " character as an alternate character string delimiter.

To escape a ' character within a literal string, type two in a row.

 

oreplace(address,'''','')

 

For single-character replacement, otranslate may be more efficient.

Teradata Employee

Re: Teradata remove single quotes from variable

This is hard to read on this web page - at least it is for me!  Basically the answer is don't use doub;e-quotes here.  Copy and paste this into a place where you can tell double single-quotes from single double-quotes:    oreplace(address,'''','')