Home » SQL & PL/SQL » SQL & PL/SQL » escape function
escape function [message #275732] Mon, 22 October 2007 09:34 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello sir,

i want to replace ('') by ("), which user entered it by mistake.
now i unable to solve this problem.

few are the queries to find out the data--


SELECT VC_COLOUR_DESC,VC_CATEGORY_CODE FROM MST_COLOUR
WHERE VC_COLOUR_DESC LIKE '%''';


VC_COLOUR_DESC	VC_CATEGORY_CODE

9.5''X59''	XWTXXX
9.5''X49''	XWTXXX
9.5''X43''	XTPXXX
9.5''X38''	XTPXXX



SELECT VC_COLOUR_DESC,VC_CATEGORY_CODE FROM MST_COLOUR
WHERE VC_COLOUR_DESC LIKE '%"';

VC_COLOUR_DESC	VC_CATEGORY_CODE
3"X5"	XWTXXX
3"X4.5"	XWTXXX
3"X6"	XWTXXX
2"X4"	XRDXXX
5.5"X7.5"	XMLXXX



i have done few queries but unable to solve the problem..

UPDATE MST_COLOUR
SET VC_COLOUR_DESC ='%"'
WHERE VC_COLOUR_DESC LIKE '%''';


row's updated --- but wrong entry

SELECT * FROM mst_colour
WHERE SUBSTR(vc_colour_desc, INSTR(vc_colour_desc,'X', 1)-2, 2) LIKE '%''' ESCAPE '%'

please help me out.



regards
Re: escape function [message #275734 is a reply to message #275732] Mon, 22 October 2007 09:38 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I suggest you look at the REPLACE function.
Re: escape function [message #275748 is a reply to message #275734] Mon, 22 October 2007 10:25 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello


SELECT replace(VC_COLOUR_DESC, '''', '"') FROM MST_COLOUR;
REPLACE(VC_COLOUR_DESC,'''','"
NA
NA
NA
NA
NA
NA
NA
NA


now tell me how could i do.

have i done something wrong.

waiting for ur reply

regards
Re: escape function [message #275751 is a reply to message #275748] Mon, 22 October 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ a where clause is missing
2/ double the number of '

Regards
Michel
Re: escape function [message #275753 is a reply to message #275732] Mon, 22 October 2007 10:29 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select replace('9.5''''X59''''', '''''', '"') from dual

9.5"X59"

[Updated on: Mon, 22 October 2007 10:29]

Report message to a moderator

Re: escape function [message #275759 is a reply to message #275753] Mon, 22 October 2007 10:36 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
dear Cthulhu

i know its good idea to replace one by one, but i want to replace datas of column(VC_COLOUR_DESC).

i dont want to give where condition, i want to update the rows of that column.

please provide me some good help dear.

i m in puzzle and unable to solve...
still looking in google to find out the solution.

regards
Re: escape function [message #275761 is a reply to message #275759] Mon, 22 October 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my point 2.

Regards
Michel
Re: escape function [message #275763 is a reply to message #275732] Mon, 22 October 2007 10:42 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I was demonstrating the technique. I would expect translating it to an update statement to be pretty trivial, but if you must be spoonfed the solution:

UPDATE MST_COLOUR
SET VC_COLOUR_DESC =replace(vc_colour_desc, '''''', '"')
WHERE VC_COLOUR_DESC LIKE '%''''%';

Re: escape function [message #275767 is a reply to message #275761] Mon, 22 October 2007 10:53 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
yes sir
there is double quotes which i want to replace by single character, how can i do.

i use where condition but unable to get my required output.


SELECT REPLACE(VC_COLOUR_DESC, '''', '" ') FROM MST_COLOUR
WHERE VC_CATEGORY_CODE LIKE 'XWTXXX';

output is...
REPLACE(VC_COLOUR_DESC,'''','"
1.5" " 
2" " 
8" 6" "  (2X2)
10" 1" "  (2X2)
10" 9" "  (2X2)
11" 6" "  (2X2)



regards
Re: escape function [message #275768 is a reply to message #275732] Mon, 22 October 2007 11:03 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You really don't seem to be getting this. To represent a single quote (') character, you need:

''''


To represent two quotes, you need:

''''''


The first and last quote delimit the string. All pairs of quotes inside the string are translated to a single quote by Oracle.

Try it out from a select command like the one I gave earlier. You'll eventually get the idea.

[Updated on: Mon, 22 October 2007 11:03]

Report message to a moderator

Re: escape function [message #275769 is a reply to message #275767] Mon, 22 October 2007 11:05 Go to previous message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanxx Cthulhu

yes i got the point..

Previous Topic: Problem in hh:min:sec?
Next Topic: Compare two tables
Goto Forum:
  


Current Time: Fri Dec 09 15:39:43 CST 2016

Total time taken to generate the page: 0.18295 seconds