INITCAP sql function and apostrophes [message #436111] |
Mon, 21 December 2009 11:02  |
ma04
Messages: 15 Registered: October 2009 Location: swindon, uk
|
Junior Member |
|
|
Hi
I can't remember if there's a way to use initcap function to display words as follows:
st. michael's hill --> St. Michael's Hill
select initcap(st. michael's hill)
from dual
gives St. Michael'S Hill
I need to use this in a database function that returns an address for a given id. so can't use sqlplus.
any ideas or refreshing my memory would be much appreciated.
regards
MA
|
|
|
|
|
|
Re: INITCAP sql function and apostrophes [message #436122 is a reply to message #436116] |
Mon, 21 December 2009 11:39   |
ma04
Messages: 15 Registered: October 2009 Location: swindon, uk
|
Junior Member |
|
|
i'm not querying it in sqlplus.
the data is in a table and a report is displaying it using a db function. this function's select is using initcap around a column name. tha data could be (michael's) or something else like (i've) or (i'm).
|
|
|
|
|
Re: INITCAP sql function and apostrophes [message #436144 is a reply to message #436139] |
Mon, 21 December 2009 17:11   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
ma01 wrote on Mon, 21 December 2009 22:45thank you MC for your expert input.
i was hoping that someone would share their expertise in this other than just ... stating the obvious .......
MA
I'm tired of the crappy attitde that some people have when they are looking for help from others without having to actually do any of their own work or having to pay for it.
Here's an idea. Instead of behaving like a smart arse, how about you post exctly what your problem is and what you are trying to achieve instead of posting syntactically incorrect problems and allusions as to what you are trying to achieve. Nobody here owes you an answer, so if you ask for help, have some common courtesy about how you do it.
Thank you.
|
|
|
Re: INITCAP sql function and apostrophes [message #436205 is a reply to message #436111] |
Tue, 22 December 2009 01:56   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
In fairness I thought the OPs problem is fairly obvious even though their example is missing quotes.
Initcap is upper casing letters after apostrophes when it really should only do letters after spaces.
|
|
|
|
|
Re: INITCAP sql function and apostrophes [message #436213 is a reply to message #436212] |
Tue, 22 December 2009 02:54   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you can guarantee that you don't use any of the extended characters in the strings, then you can do this:select replace(initcap(replace('st michael''s hill','''',chr(255))),chr(255),'''') from dual;
|
|
|
|
|
Re: INITCAP sql function and apostrophes [message #436499 is a reply to message #436223] |
Wed, 23 December 2009 19:15   |
ma04
Messages: 15 Registered: October 2009 Location: swindon, uk
|
Junior Member |
|
|
thanks gents for your help.
pablolee : attitude ... mmmmm .... just like yours???
thanks to others seniors.
i did get the solution as i thought it was something to do with replace function.
my tuppence worth (a bit similar to an earlier suggestion) but possibly a bit more efficient):
select replace(initcap(instring), chr(39)||'S', chr(39)||'s')
from dual
assuming there's always an "s" after the apostrophe.
OR (a more generic solution)
select replace(
initcap(
replace(instring,
chr(39), 'xxxxxxx'),
'xxxxxxx', chr(39))
from dual
thanks and regards
MA
|
|
|
|
Re: INITCAP sql function and apostrophes [message #436632 is a reply to message #436545] |
Thu, 24 December 2009 12:38   |
ma04
Messages: 15 Registered: October 2009 Location: swindon, uk
|
Junior Member |
|
|
this is getting silly now ...
i thought that this is the place for discussion and sharing opinions on technical issue/problems!!
having a go/moan and speaking up for someone else should not be the norm here.
everyone is asking/hoping for help here to a certain extent.
people should limit their views to technical issues.
if someone is not keen to share their constructive thoughts then at least have courtesy to keep out of a little verbal joust.
if one makes a smart arse remark then one should be able to entertain a response too!!
|
|
|
Re: INITCAP sql function and apostrophes [message #436676 is a reply to message #436632] |
Fri, 25 December 2009 03:50  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:having a go/moan and speaking up for someone else should not be the norm here.
Ah, it is YOUR site?
Quote:people should limit their views to technical issues.
Like yours?
Quote:if someone is not keen to share their constructive thoughts then at least have courtesy to keep out of a little verbal joust.
Like you?
Quote:if one makes a smart arse remark then one should be able to entertain a response too!!
But only from you?
Regards
Michel
|
|
|