CODEWITHSUNDEEP
sqlplsqltrim
jonasespelita
jonasespelita

Reputation: 1650

Trim the ' (apostrophe) character in an sql statement

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?

Upvotes: 0

Views: 5300

Answers (3)

CMG
CMG

Reputation: 371

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.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

This will remove leading and trailing apostrophes from a value:

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

or if you prefer:

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

Upvotes: 1

dave
dave

Reputation: 11975

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?)

Upvotes: 1

Related Questions