Home » SQL & PL/SQL » SQL & PL/SQL » replacing single quote while loading data (oracle 9.2.0.8)
replacing single quote while loading data [message #360393] Thu, 20 November 2008 17:08 Go to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
Hello,

I wanted to remove any single quote or double quote in the Description field while loading the data..

Here is my trigger to remove double quote. How can i remove single quote... Any help is appreciated...


create or replace trigger trg_temp_load 
before insert on temp
for each row
begin
:new."Description" := translate(:new."Description",'A"','A');
end;
/


Thanks
Re: replacing single quote while loading data [message #360394 is a reply to message #360393] Thu, 20 November 2008 18:48 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> with t as (
  2       select 'that''s a single quote and a "double" quote' x from dual)
  3  select translate(x, ' '||chr(39)||chr(34), ' ' ) from t;

TRANSLATE(X,''||CHR(39)||CHR(34),'')
---------------------------------------
thats a single quote and a double quote

Re: replacing single quote while loading data [message #360398 is a reply to message #360394] Thu, 20 November 2008 20:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
or you could do it the old fashioned way and use four tick marks.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select '''' from dual;

'
-
'

SQL> 


If the above seems confusing then consider this:

SQL> select 'abc''def' from dual;

'ABC''D
-------
abc'def

SQL> 


Remember that you double up the tick marks to create a single quote when inside a quoted string. If we then remove the abc and def, you are left with '''' or a single quote.

Kevin x79427
Re: replacing single quote while loading data [message #360400 is a reply to message #360398] Thu, 20 November 2008 20:39 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I think the OP was looking to replace the quotes.
Re: replacing single quote while loading data [message #360402 is a reply to message #360400] Thu, 20 November 2008 20:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
No, the OP already knows how to replace things as is witnesed by their posted code that uses TRANSLATE. The problem for the OP is in knowing how to represent a single quote inside a quoted string so that it can be replaced (or removed).

But, since this is about teaching, let us offer up an alternative to translate.


  1* select replace('abc''def','''',null) from dual
SQL> /

REPLAC
------
abcdef

SQL> 


For this, the OP should read the docs on REPLACE.

Sorry ebrian, I didn't mean for my reply to sound rude.

Kevin

[Updated on: Thu, 20 November 2008 20:59]

Report message to a moderator

Re: replacing single quote while loading data [message #360417 is a reply to message #360402] Thu, 20 November 2008 22:15 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
No worries Kevin.

However, the REPLACE won't work in the OP's case unless it is doubled up.
Re: replacing single quote while loading data [message #360420 is a reply to message #360417] Thu, 20 November 2008 22:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Sure it will. OP only needs to double up if the string being worked is a quoted string. Most of the time we work with variables that hold string values. Either translate or replace will work just fine.

If however the OP is indeed using true quoted text strings in the problem space then as you point out, the OP will have to adapt the solution.

Thanks for you comments, Kevin.
Re: replacing single quote while loading data [message #360423 is a reply to message #360420] Thu, 20 November 2008 22:51 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I must be missing something here ? The OP asked to remove all double and single quotes from the Description field. A single REPLACE won't be able to do that if a row includes both of those characters (unless of course those characters are actually next to each other).

SQL> with t as (
  2     select 'that''s a single quote and a "double" quote' x from dual)
  3  select replace(x, '''"') from t;

REPLACE(X,'''"')
------------------------------------------
that's a single quote and a "double" quote

Re: replacing single quote while loading data [message #360450 is a reply to message #360393] Fri, 21 November 2008 01:05 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No worries ,

Actually Both will work Smile

SQL> with t as (
  2        select 'that''s a "single quote and a "double" quote' x from dual)
  3     select Translate(x, 'a''"','a') y from t;

Y
---------------------------------------
thats a single quote and a double quote

SQL> with t as (
  2        select 'that''s a "single quote and a "double" quote' x from dual)
  3     select REPLACE(REPLACE(x, ''''),'"') y from t;

Y
---------------------------------------
thats a single quote and a double quote

SQL>



Smile
Rajuvan.
Re: replacing single quote while loading data [message #360588 is a reply to message #360450] Fri, 21 November 2008 08:50 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
rajavu1 wrote on Fri, 21 November 2008 02:05

Actually Both will work Smile

Correct, that was my point.

ebrian wrote on Thu, 20 November 2008 23:15

However, the REPLACE won't work in the OP's case unless it is doubled up.

Previous Topic: dynamic sql problem
Next Topic: Sorting logic not working for all data conditions
Goto Forum:
  


Current Time: Sat Dec 10 01:29:28 CST 2016

Total time taken to generate the page: 0.12673 seconds