Trim the ' (apostrophe) character in an sql statement

Question

I'm using the DBMS_SQL package that returns the value '12345' as a value for a column name.

How do you trim the apostrophes out from the value so that it could be converted into a number?

Answer 1

Something like the following should trim the apostrophes:

substr(columnName, 2, length(columnName) - 2)

(As an aside, it's a pretty odd DB with a column name that's an integer, isn't it?)

Answer 2

This will remove leading and trailing apostrophes from a value:

RTRIM(LTRIM(value,''''),'''')

or if you prefer:

RTRIM(LTRIM(value,chr(39)),chr(39))

Answer 3

you could just use the "REPLACE" function to replace all single-quotes in the string with NULLs.

eg . with quotes : select 'Hello,''World''' from dual

quotes removed: select replace('Hello,''World''','''',NULL) from dual

But, a columnname in Oracle cannot begin with a digit, so 12345 is invalid as a column-name anyway.