Home » SQL & PL/SQL » SQL & PL/SQL » SQL - Escape character
SQL - Escape character [message #345866] Fri, 05 September 2008 02:53 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Pls go thru the attachment & provide me the correct query to pull out the '.' symbol associated qualification.

Re: SQL - Escape character [message #345870 is a reply to message #345866] Fri, 05 September 2008 03:09 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It must be me (as I don't know what you, actually, need) but, if you'd want to find people whose "qualification" column value contains a dot ".", you could use INSTR function as
select * from your_table
where instr(qualification, '.') > 0
Re: SQL - Escape character [message #345873 is a reply to message #345870] Fri, 05 September 2008 03:18 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thanks a lot! it's working but actually i need to pull out the qualification column values where '.' symbol is associated using escape character. It's working for other symbols('_', '%', '/' etc) but not for '.' dot. Pls give me the query using escape character.
Re: SQL - Escape character [message #345875 is a reply to message #345873] Fri, 05 September 2008 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain in detail possibly with an example what you need, it is unclear.

Regards
Michel
Re: SQL - Escape character [message #345880 is a reply to message #345875] Fri, 05 September 2008 03:35 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

It's working for other symbols('_', '%', '/' etc) but not for '.' dot



Post It.

Quote:

Pls give me the query using escape character.



Post a test Case Create Table And Insert Statements.

Regards,
Rajat
Re: SQL - Escape character [message #346090 is a reply to message #345875] Fri, 05 September 2008 20:06 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
I have attached text file(with example)..pls go thru that hope it will help and give me correct query to pull out the data.
Re: SQL - Escape character [message #346101 is a reply to message #346090] Sat, 06 September 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to escape '.', it is not a joker character for Oracle.

Regards
Michel
Re: SQL - Escape character [message #346103 is a reply to message #346101] Sat, 06 September 2008 00:51 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Then suggest me the correct query and explain in detail. what do you mean by this?...i didn't understand (You don't need to escape '.', it is not a joker character for Oracle.)
Re: SQL - Escape character [message #346108 is a reply to message #346103] Sat, 06 September 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'.' is treated in the same way than say 'A'.

Regards
Michel
icon7.gif  Re: SQL - Escape character [message #346111 is a reply to message #346108] Sat, 06 September 2008 01:24 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thanks a lot. now i got it..it working!!! Smile
Re: SQL - Escape character [message #395290 is a reply to message #346108] Wed, 01 April 2009 01:35 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
'.' is treated in the same way than say 'A'.

I am satisfied with the above answer but I have doubt why Oracle is considering '.' as character and not as symbol such as '_', '%', '/'. Is that an Oracle Bug or is there any specific reason?
Re: SQL - Escape character [message #395292 is a reply to message #395290] Wed, 01 April 2009 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'_', '%' are the only wild characters for LIKE operator and so they are the only ones you need to espace (with the escape character itself).
You don't need to and can't escape '/' (unless it is the escape character you choose).

Regards
Michel
Re: SQL - Escape character [message #395296 is a reply to message #395292] Wed, 01 April 2009 01:50 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thanks but why Oracle is considering '.' as character?
Re: SQL - Escape character [message #395302 is a reply to message #395296] Wed, 01 April 2009 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because it is a character as well as / , ; and other special characters (but it is not an alphabetical character).

Regards
Michel
Re: SQL - Escape character [message #395317 is a reply to message #395302] Wed, 01 April 2009 02:25 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Sorry, i didnot understand. Can you please explain in brief?
Re: SQL - Escape character [message #395324 is a reply to message #395317] Wed, 01 April 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but I don't understand what you don't understand.
All what is in a character set is a character?
Among these, they are printable characters and not printable characters.
Among the former ones they are alphabetical characters, digits and other characters which can be special characters and not special characters depending on the usage. For instance '_' is a special character for LIKE, it is not for an identifier. At the opposite '.' is not a special character for LIKE, it is for an identifier.

Regards
Michel

Re: SQL - Escape character [message #395345 is a reply to message #395324] Wed, 01 April 2009 03:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that you did not read the text of the error message that you got:
ORA-01424:	missing or illegal character following the escape character
Cause:	The character following the escape character in LIKE pattern is missing or not one of the escape character, '%', or '_'.
Action:	Remove the escape character or specify the missing character. 


I think that makes the cause of your problem pretty clear.
Previous Topic: How to process every line of a table
Next Topic: Extract Data within the Parenthesis
Goto Forum:
  


Current Time: Thu Dec 08 00:22:38 CST 2016

Total time taken to generate the page: 0.12160 seconds