CODEWITHSUNDEEP
oracle-database
Rohit Poddar
Rohit Poddar

Reputation: 1

Oracle SQL: Using Replace function while Inserting

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?

Upvotes: 0

Views: 1626

Answers (4)

eifla001
eifla001

Reputation: 1157

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

Upvotes: 0

Caroline
Caroline

Reputation: 242

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!', ''''))

Upvotes: 1

theo
theo

Reputation: 299

Single quotes are escaped by doubling them up

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

Upvotes: 0

Jayanth
Jayanth

Reputation: 816

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

Upvotes: 1

Related Questions