Home » SQL & PL/SQL » SQL & PL/SQL » Representing Commas
Representing Commas [message #236736] Thu, 10 May 2007 15:21 Go to next message
RCL1970
Messages: 4
Registered: May 2007
Location: St. Louis, Missouri USA
Junior Member
Hopefully this is an easy one:

I'm writing a routine that takes the first three letters of a name. The catch is that there is a list of characters that I don't want to include.

So here's the line I've got:

IF a1 NOT IN(' ','.',',','&','-','/') THEN

Note, the list of characters I don't want. Here's my problem...how do I include a comma, in my list? Obviously, three single commas won't work, or it'll think I've got one "open" comma.

Thanks in advance for the help!
Re: Representing Commas [message #236738 is a reply to message #236736] Thu, 10 May 2007 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why (...,',') does not work?

Regards
Michel
Re: Representing Commas [message #236739 is a reply to message #236738] Thu, 10 May 2007 15:31 Go to previous messageGo to next message
RCL1970
Messages: 4
Registered: May 2007
Location: St. Louis, Missouri USA
Junior Member
Oh, sorry....

I meant a single quote...not a comma. I would think three single quotes = 1½ sets of quotes.
Re: Representing Commas [message #236740 is a reply to message #236739] Thu, 10 May 2007 15:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select '''' from dual;
'
-
'

1 row selected.

Regards
Michel
Re: Representing Commas (escaping quotes) [message #236819 is a reply to message #236740] Fri, 11 May 2007 01:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yep, Michel has nailed it (yet again): in order to escape a quote you need to double it.

MHE
Re: Representing Commas [message #236823 is a reply to message #236740] Fri, 11 May 2007 01:43 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Don't you think using the escape character would have been a better solution?
Re: Representing Commas [message #236824 is a reply to message #236823] Fri, 11 May 2007 01:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
In PL/SQL? Please elaborate.

MHE
Re: Representing Commas [message #236825 is a reply to message #236823] Fri, 11 May 2007 01:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And how would you escape a quote?
Re: Representing Commas [message #236828 is a reply to message #236825] Fri, 11 May 2007 01:51 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
system@MYDB-SQL>>set escape on
system@MYDB-SQL>>select 'tom & jerry' from dual;
Enter value for jerry: 
old   1: select 'tom & jerry' from dual
new   1: select 'tom ' from dual

'TOM
----
tom

system@MYDB-SQL>>select 'tom \& jerry' from dual;

'TOM&JERRY'
-----------
tom & jerry

system@MYDB-SQL>>

This is what I had in mind, but yes, if you are using PL/SQL, then I guess this 'tweak' through SQL*Plus wouldn't work
Re: Representing Commas [message #236831 is a reply to message #236828] Fri, 11 May 2007 01:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And now, try to escape a single quote.

MHE
Re: Representing Commas [message #236834 is a reply to message #236831] Fri, 11 May 2007 02:01 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
system@MYDB-SQL>>set escape ''''
system@MYDB-SQL>>select ''saibal'' from dual;

'SAIBA
------
saibal

system@MYDB-SQL>>
Re: Representing Commas [message #236839 is a reply to message #236834] Fri, 11 May 2007 02:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And you did that how? Indeed, like Michel: doubling the quotes.

MHE
Re: Representing Commas [message #236843 is a reply to message #236839] Fri, 11 May 2007 02:21 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
You asked me to show how to escape a single quote and I showed it. And I used escape to do it, Michel didn't.
Regards
Re: Representing Commas [message #236847 is a reply to message #236843] Fri, 11 May 2007 02:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but I don't see a quote in your output..
Re: Representing Commas [message #236869 is a reply to message #236847] Fri, 11 May 2007 03:22 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Were you expecting 'saibal' as the output? Even Michel's method won't give you that. Have a look below:

system@MYDB-SQL>>set escape off
system@MYDB-SQL>>select ''saibal'' from dual;--Michel's method
select ''saibal'' from dual
               *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


system@MYDB-SQL>>select "'saibal'" from dual;
select "'saibal'" from dual
       *
ERROR at line 1:
ORA-00904: "'saibal'": invalid identifier


system@MYDB-SQL>>select '"saibal"' from dual;

'"SAIBAL
--------
"saibal"

system@MYDB-SQL>>


As a corollary to the above, how will you execute the following snippet of code
begin
dbms_output.put_line('frank & maaher's territory');
end;

Hoping to learn something on this
Regards
Re: Representing Commas [message #236879 is a reply to message #236869] Fri, 11 May 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant:
SQL> select '''michel''' from dual;
'''MICHE
--------
'michel'

1 row selected.

And for you last point:
SQL> set define off
SQL> begin
  2  dbms_output.put_line('frank & maaher''s territory');
  3  end;
  4  /
frank & maaher's territory

PL/SQL procedure successfully completed.

Regards
Michel
Re: Representing Commas [message #236885 is a reply to message #236736] Fri, 11 May 2007 03:46 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
If you are using 10g the use the very cool, quoted string delimeter:

begin
dbms_output.put_line(q'!I'm puttin' as many of the "'" thing's in as I wan't to''!');
dbms_output.put_line(q'[Or here's using a diffrn't delmiter'''']');
end;

I'm puttin' as many of the "'" thing's in as I wan't to''
Or here's using a diffrn't delmiter''''



[Updated on: Fri, 11 May 2007 03:47]

Report message to a moderator

Re: Representing Commas [message #236886 is a reply to message #236879] Fri, 11 May 2007 03:47 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Thanx Michel. But, I can't reproduce your example:
system@MYDB-SQL>>begin
  2  dbms_output.put_line('frank & maher''s territory');
  3  end;
  4  /
Enter value for maher: 


I would have done it this way:

system@MYDB-SQL>>set escape on
system@MYDB-SQL>>sho escape
escape "\" (hex 5c)
system@MYDB-SQL>>begin
  2  dbms_output.put_line('frank \& maaher''s territory');
  3  end;
  4  /
frank & maaher's territory


PL/SQL procedure successfully completed.
Re: Representing Commas [message #236889 is a reply to message #236886] Fri, 11 May 2007 03:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
SET DEFINE OFF

as per Michel's example
Re: Representing Commas [message #236893 is a reply to message #236889] Fri, 11 May 2007 03:55 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Thanks. Everything's fine now. And the dust is just settling.
Re: Representing Commas [message #236900 is a reply to message #236893] Fri, 11 May 2007 04:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
saibal,
No offense intended (people seem to have a short fuse these days Very Happy)

However, you gave me the idea of searching the manuals for another way of escaping (thank you for that), and I came across the following interesting text:
OracleŽ Database PL/SQL User's Guide and Reference 10g Release 2 (10.2)
For literals where doubling the quotes is inconvenient or hard to read, you can designate an escape character using the notation q'esc_char ... esc_char'. This escape character must not occur anywhere else inside the string.


So you showed me the right direction, saibal: you can use an escape character. Here's a demo:

SQL> SELECT q'XSaibal's idea of 'escaping'X' test
  2  FROM dual
  3  /

TEST
---------------------------
Saibal's idea of 'escaping'


One to remember.

[Edit]I doubt that this works in older versions though (tested on 10gR2)

MHE

[Updated on: Fri, 11 May 2007 04:40]

Report message to a moderator

Re: Representing Commas [message #236901 is a reply to message #236900] Fri, 11 May 2007 04:41 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Which I demonstrated about 5 posts above Smile
Re: Representing Commas [message #236902 is a reply to message #236901] Fri, 11 May 2007 04:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
pablolee wrote on Fri, 11 May 2007 11:41
Which I demonstrated about 5 posts above Smile

Fair enough. I missed the replies after Frank's (posted at 09:30) and just hit reply. Shame on me for not reading an entire thread Sad. But it still was saibal that gave me the idea.

MHE
Re: Representing Commas [message #236905 is a reply to message #236900] Fri, 11 May 2007 04:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
LOL.
So this is a way to avoid 'hard to read' alternatives?
I find the way described much harder to read. But maybe I'm getting old..
Re: Representing Commas [message #236906 is a reply to message #236900] Fri, 11 May 2007 04:55 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Quote:
saibal,
No offense intended (people seem to have a short fuse these days )

However, you gave me the idea of searching the manuals for another way of escaping (thank you for that), and I came across the following interesting text:


Maaher no offense(sic) taken in any form, shape or size. I wasn't searching the manuals for another way of 'escaping', but yes, I was testing it on Oracle 10g Release 1. And indeed, I feel elated that I learn t something new today on a pretty mundane topic. However, to flog a dead horse and to lay this ghost to rest once and for all, did you find anyway to escape ampersand without setting anything in SQL*Plus? If so, there is one thing more I shall be learning today!
Re: Representing Commas [message #236942 is a reply to message #236906] Fri, 11 May 2007 06:52 Go to previous messageGo to next message
RCL1970
Messages: 4
Registered: May 2007
Location: St. Louis, Missouri USA
Junior Member
Thanks for all the help. However, last night a light came on, and I tried representing my single quote as "chr(39)" and it worked. I should have thought of that, but I guess I needed a couple shots of booze to get me in a different frame of thought. Smile
Re: Representing Commas [message #236951 is a reply to message #236942] Fri, 11 May 2007 07:06 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was an interesting question as it led to many different and useful solutions.

Regards
Michel
Previous Topic: Complex Calculations
Next Topic: How to natural join 3 tables efficiently?
Goto Forum:
  


Current Time: Sat Dec 03 03:38:44 CST 2016

Total time taken to generate the page: 0.08104 seconds