| Apostrophe vs. quoted string In Oracle [message #10287] |
Tue, 13 January 2004 05:24  |
denise
Messages: 64
Registered: November 2000
|
Member |
|
|
Hello
have a dilemma in which I need to update last names
in a table with apostrophe's.
example: O`Brien
management wants the (`) replaced with (')...however
the apostrophe character is also the character Oracle
uses for string quotation...i.e. 'O'Brien'.
I want to create a sql statement like the following:
select replace(last_name,'O`','O''....herein lies the problem?
any tips greatly appreciated.
|
|
|
|
| Re: Apostrophe vs. quoted string In Oracle [message #10288 is a reply to message #10287] |
Tue, 13 January 2004 05:41  |
denise
Messages: 64
Registered: November 2000
|
Member |
|
|
never mind....figured it out...thanks.
this is for anyone else who comes across this scenario.
1 select replace(last,'O`','O''')
2* from prospect where idnumber=2303365
SQL> /
REPLACE(LAST,'O`','O''')
------------------------------------------------------
O'Brien
SQL>
|
|
|
|
|
|
| Re: Apostrophe vs. quoted string In Oracle [message #10354 is a reply to message #10293] |
Fri, 16 January 2004 10:03 |
denise
Messages: 64
Registered: November 2000
|
Member |
|
|
update customer
set lastname=replace(lastname,'O`','O''')
where lastname LIKE 'O`%'
the above will take all occurences of O` and convert
them to O'.
you could also substitute:
update customer
set lastname=replace(lastname,'O`','O''')
where lastname LIKE 'O`Brien' (or LIKE 'O`B%')....
whatever variations you want to use in the where
clause.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
