| A function to replace CAPS [message #316718] |
Mon, 28 April 2008 00:59  |
anna marie Messages: 20 Registered: June 2006 Location: Philippines |
Junior Member |
|
|
I'm doing this:
SELECT 'StringThatMustCorrected' --this is column name on my table
from dual
Now, I want to put space before each capital letter.
Is there any function to detect CAPS so that I can replace it?
|
|
|
| Re: A function to replace CAPS [message #316720 is a reply to message #316718 ] |
Mon, 28 April 2008 01:03   |
anacedent Messages: 5027 Registered: July 2005 Location: surf meets turf in SoCal |
Senior Member |
|
|
SQL Experts
Advanced Oracle SQL questions - Complex queries, DML and DDL statements. Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
This seems to be a homework assignment;
which has its own sub-forum below.
>Is there any function to detect CAPS so that I can replace it?
YES, kind of.
|
|
|
| Re: A function to replace CAPS [message #316740 is a reply to message #316720 ] |
Mon, 28 April 2008 02:44   |
rleishman Messages: 2563 Registered: October 2005 Location: Melbourne, Australia |
Senior Member |
|
|
Something like this perhaps?
select regexp_replace(StringThatMustCorrected, '([A-Z])', ' \\1')
from TableThatMustBeCorrected
Ross Leishman
|
|
|
| Re: A function to replace CAPS [message #316911 is a reply to message #316718 ] |
Mon, 28 April 2008 19:36   |
 |
ebrian Messages: 1883 Registered: April 2006 |
Senior Member |
|
|
If you actually want a space before the first letter in the string, then you can remove the LTRIM function:
SQL> with t as (
2 select 'StringThatMustCorrected' val from dual union all
3 select 'ABC' from dual)
4 select ltrim(regexp_replace(t.val, '([A-Z])', ' \1')) val from t;
VAL
------------------------------
String That Must Corrected
A B C
[Updated on: Mon, 28 April 2008 19:39]
|
|
|
| Re: A function to replace CAPS [message #316942 is a reply to message #316720 ] |
Mon, 28 April 2008 21:59   |
anna marie Messages: 20 Registered: June 2006 Location: Philippines |
Junior Member |
|
|
| anacedent wrote on Mon, 28 April 2008 01:03 | SQL Experts
Advanced Oracle SQL questions - Complex queries, DML and DDL statements. Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
Newbies should not post to this forum!
This seems to be a homework assignment;
which has its own sub-forum below.
>Is there any function to detect CAPS so that I can replace it?
YES, kind of.
|
not an assignment, not a newbie either...
I'm just new to the company so that i have no priviledge to create my own function. can't find solution also by googling the web, so i think this is not a newbie question.
thanks people for sharing answers.
|
|
|
| Re: A function to replace CAPS [message #316948 is a reply to message #316942 ] |
Mon, 28 April 2008 23:00   |
Michel Cadot Messages: 15238 Registered: March 2007 Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | I'm just new to the company so that i have no priviledge to create my own function. can't find solution also by googling the web, so i think this is not a newbie question.
|
You don't need to create a function as previous posts showed it. And not having the privilege is NOT a reason to post in Expert forum maybe the opposite.
Not finding a solution with Google is NOT a reason to post in Expert forum otherwise must of newbie questions should be in Expert forum.
As the answers showed it, you can't say YES to the question: "Do I think the problem at hand is a difficult one?" or, if you can, you can't say YES to "Do I consider myself an expert?" or you lie to yourself.
Read Not an EXPERT? Post in the NEWBIES forum, NOT here
Regards
Michel
|
|
|
| Re: A function to replace CAPS [message #317016 is a reply to message #316948 ] |
Tue, 29 April 2008 03:39   |
Frank Messages: 5230 Registered: April 2002 Location: NL |
Senior Member |
|
|
I have to disagree here.
This problem is a tricky one; the fact that someone thinks about using regexp, which makes it quite easy after all, is not relevant.
In retrospect, given the solution, almost all problems look easy.
[Updated on: Tue, 29 April 2008 03:39]
|
|
|
| Re: A function to replace CAPS [message #317041 is a reply to message #316718 ] |
Tue, 29 April 2008 05:12   |
suresh_oradba Messages: 4 Registered: September 2007 |
Junior Member |
|
|
|
Pre-10G, when regexp functions are not available, any solution to the problem please?
|
|
|
| Re: A function to replace CAPS [message #317046 is a reply to message #317041 ] |
Tue, 29 April 2008 05:27   |
Michel Cadot Messages: 15238 Registered: March 2007 Location: Nanterre, France, http://... |
Senior Member |
|
|
What did you try?
Regards
Michel
|
|
|
| Re: A function to replace CAPS [message #317053 is a reply to message #316942 ] |
Tue, 29 April 2008 06:05   |
S.Rajaram Messages: 516 Registered: October 2006 Location: United Kingdom |
Senior Member |
|
|
| Quote: |
can't find solution also by googling the web, so i think this is not a newbie question.
|
I don't think so. I found this link by googling it. This is one way of doing it. My search in google is very simple. Text I keyed in is "inserting space in a string in oracle".
http://www.oracle.com/technology/oramag/code/tips2006/100206.html
Regards
Raj
P.S : Forgot to mention, solution mentioned in the link needs a little tweak to satisfy the criteria. Having said that I am not sure about the performance.
[Updated on: Tue, 29 April 2008 06:07]
|
|
|
| Re: A function to replace CAPS [message #318106 is a reply to message #317053 ] |
Mon, 05 May 2008 11:39   |
andrew again Messages: 2150 Registered: September 2004 |
Senior Member |
|
|
|
I agree, this is a perfectly acceptable question for this forum. I'm way beyond newbie status and didn't have a clue to a solution.
|
|
|
| Re: A function to replace CAPS [message #318131 is a reply to message #316718 ] |
Mon, 05 May 2008 18:18   |
anna marie Messages: 20 Registered: June 2006 Location: Philippines |
Junior Member |
|
|
|
still can't solve it. regexp is not available for me.
|
|
|
| Re: A function to replace CAPS [message #318133 is a reply to message #316718 ] |
Mon, 05 May 2008 18:33   |
anacedent Messages: 5027 Registered: July 2005 Location: surf meets turf in SoCal |
Senior Member |
|
|
SELECT replace(replace(replace(replace('StringThatMustCorrected','S',' S'),'T',' T'),'M',' M'),'C',' C') from dual;
REPLACE(REPLACE(REPLACE(REP
---------------------------
String That Must Corrected
|
|
|
| Re: A function to replace CAPS [message #318181 is a reply to message #318133 ] |
Tue, 06 May 2008 01:03  |
Frank Messages: 5230 Registered: April 2002 Location: NL |
Senior Member |
|
|
A more generic way (probably can be optimized)
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> with data as (select '&theText' theText
2 from dual
3 )
4 select ltrim(max(replace(sys_connect_by_path(newLetter, '/'), '/')))
5 from (select case when letter = upper(letter)
6 then ' '||letter
7 else letter
8 end newLetter
9 , rn
10 from (select substr(theText, rn, 1) letter
11 , rn
12 from (select level as rn
13 , data.theText
14 from data
15 connect by level <= length(data.theText)
16 )
17 )
18 )
19 connect by rn = prior rn + 1
20 start with rn = 1;
Enter value for thetext: ThisMightBeDoneBetter
old 1: with data as (select '&theText' theText
new 1: with data as (select 'ThisMightBeDoneBetter' theText
LTRIM(MAX(REPLACE(SYS_CONNECT_BY_PATH(NEWLETTER,'/'),'/')))
-------------------------------------------------------------------------
This Might Be Done Better
|
|
|