Oracle SQL: Using Replace function while Inserting

Question

I have a query like this:

INSERT INTO TAB_AUTOCRCMTREQUESTS 
 (RequestOrigin, RequestKey, CommentText) VALUES ('Tracker', 'OPM03865_0', '[Orange.Security.OrangePrincipal] 
 em[u02650791]okok
it's friday!')

As expected it is throwing an error of missing comma, due to this it's friday! which has a single quote.

I want to remove this single quote while inserting using Replace function. How can this be done?

What about keeping the apostrophe by escaping it, i.e. use double apostrophe as in it''s — Abra
– Abra, Commented Feb 8, 2018 at 6:03
Or use the q quoting mechanism. Instead of enclosing the text string within single quotes, enclose it within q'[text here]' docs.oracle.com/cd/B19306_01/server.102/b14200/… — user5683823
– user5683823, Commented Feb 8, 2018 at 6:10

Jayanth · Accepted Answer · 2018-02-08 06:14:07Z

1

Reason for error is because of the single Quote. In order to correct it, you shall not remove the single quote instead you need to add one more i.e. you need to make it's friday to it''s friday while inserting.

If you need to replace it for sure, then try the below code :

insert into Blagh values(REPLACE('it''s friday', '''', ''),12);

answered Feb 8, 2018 at 6:14

Jayanth

8269 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Caroline · Accepted Answer · 2018-02-08 06:54:27Z

I would suggest using Oracle q quote.

Example:

INSERT INTO TAB_AUTOCRCMTREQUESTS (RequestOrigin, RequestKey, CommentText)
VALUES ('Tracker', 'OPM03865_0', 
q'{[Orange.Security.OrangePrincipal] em[u02650791]okok it's friday!}')

You can read about q quote here. To shorten this article you will follow this format: q'{your string here}' where "{" represents the starting delimiter, and "}" represents the ending delimiter. Oracle automatically recognizes "paired" delimiters, such as [], {}, (), and <>. If you want to use some other character as your start delimiter and it doesn't have a "natural" partner for termination, you must use the same character for start and end delimiters.

Obviously you can't user [] delimiters because you have this in your queries. I sugest using {} delimiters.

Of course you can use double qoute in it it''s with replace. You can omit last parameter in replace because it isn't mandatory and without it it automatically will remove ' character.

INSERT INTO TAB_AUTOCRCMTREQUESTS (CommentText) VALUES (REPLACE('...it''s friday!', ''''))

theo · Accepted Answer · 2018-02-08 06:37:09Z

0

Single quotes are escaped by doubling them up

INSERT INTO Blagh VALUES(REPLACE('it''s friday', '''', ''),12);

answered Feb 8, 2018 at 6:37

theo

2991 gold badge3 silver badges19 bronze badges

Comments

eifla001 · Accepted Answer · 2018-02-08 09:06:16Z

0

You can try this, (sorry but I don't know why q'[ ] works)

INSERT INTO TAB_AUTOCRCMTREQUESTS 
 (RequestOrigin, RequestKey, CommentText) VALUES ('Tracker', 'OPM03865_0', q'[[Orange.Security.OrangePrincipal] em[u02650791]okok it's friday!]')

I just got the q'[] from this link Oracle pl-sql escape character (for a " ' ") - this question could be a possible duplicate

answered Feb 8, 2018 at 9:06

eifla001

1,1578 silver badges9 bronze badges

Collectives™ on Stack Overflow

Oracle SQL: Using Replace function while Inserting

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related