Home » SQL & PL/SQL » SQL & PL/SQL » INITCAP sql function and apostrophes (oracle 10g, windows xp, vista)
INITCAP sql function and apostrophes [message #436111] Mon, 21 December 2009 11:02 Go to next message
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 #436112 is a reply to message #436111] Mon, 21 December 2009 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
var_out := initcap(var_in);
Re: INITCAP sql function and apostrophes [message #436116 is a reply to message #436111] Mon, 21 December 2009 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select initcap(st. michael's hill)
from dual

gives St. Michael'S Hill

No it returns an error:
SQL> select initcap(st. michael's hill)
  2  from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select replace(initcap('st. michael''s hill'),'''S ','''s ') from dual;
REPLACE(INITCAP('S
------------------
St. Michael's Hill

1 row selected.

Regards
Michel
Re: INITCAP sql function and apostrophes [message #436119 is a reply to message #436112] Mon, 21 December 2009 11:33 Go to previous messageGo to next message
ma04
Messages: 15
Registered: October 2009
Location: swindon, uk
Junior Member
BlackSwan wrote on Mon, 21 December 2009 17:05
var_out := initcap(var_in);


bit of a typo there. it should of course have been (''s)
Re: INITCAP sql function and apostrophes [message #436122 is a reply to message #436116] Mon, 21 December 2009 11:39 Go to previous messageGo to next message
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 #436123 is a reply to message #436122] Mon, 21 December 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Create your own function.

Regards
Michel
Re: INITCAP sql function and apostrophes [message #436139 is a reply to message #436123] Mon, 21 December 2009 16:45 Go to previous messageGo to next message
ma04
Messages: 15
Registered: October 2009
Location: swindon, uk
Junior Member
thank you MC for your expert input.

i was hoping that someone would share their expertise in this other than just ... stating the obvious .......

MA
Re: INITCAP sql function and apostrophes [message #436144 is a reply to message #436139] Mon, 21 December 2009 17:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
ma01 wrote on Mon, 21 December 2009 22:45
thank 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 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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 #436211 is a reply to message #436205] Tue, 22 December 2009 02:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Both sides have a point here - the initial reply was about as much help as a swift kick the the nuts, and showed no sign of anything more than a cursory glance at the question.
Re: INITCAP sql function and apostrophes [message #436212 is a reply to message #436211] Tue, 22 December 2009 02:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a link to a thread on the Oracle Forums that discusses the same problem, and has a user function to do just this sort of initcapping
Re: INITCAP sql function and apostrophes [message #436213 is a reply to message #436212] Tue, 22 December 2009 02:54 Go to previous messageGo to next message
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 #436220 is a reply to message #436213] Tue, 22 December 2009 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But this is NOT what he wants as he answered me:
Quote:
i'm not querying it in sqlplus.

Wink

Regards
Michel
Re: INITCAP sql function and apostrophes [message #436223 is a reply to message #436220] Tue, 22 December 2009 03:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
But this is NOT what he wants as he answered me:


Ah, but I'm not giving him the solution, just a pointer in the correct direction
Cool
Re: INITCAP sql function and apostrophes [message #436499 is a reply to message #436223] Wed, 23 December 2009 19:15 Go to previous messageGo to next message
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 #436545 is a reply to message #436499] Thu, 24 December 2009 02:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
pablolee : attitude ... mmmmm .... just like yours???

The difference being that @Pablolee isn't the one hoping that people will take the time to help them, and you are.

Re: INITCAP sql function and apostrophes [message #436632 is a reply to message #436545] Thu, 24 December 2009 12:38 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: null data
Next Topic: Serial And Parallel Direct-Path Insert
Goto Forum:
  


Current Time: Wed Dec 07 03:21:54 CST 2016

Total time taken to generate the page: 0.09640 seconds