Home » SQL & PL/SQL » SQL & PL/SQL » A function to replace CAPS
A function to replace CAPS [message #316718] Mon, 28 April 2008 00:59 Go to next message
anna marie
Messages: 26
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: 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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
ebrian
Messages: 2794
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]

Report message to a moderator

Re: A function to replace CAPS [message #316942 is a reply to message #316720] Mon, 28 April 2008 21:59 Go to previous messageGo to next message
anna marie
Messages: 26
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
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]

Report message to a moderator

Re: A function to replace CAPS [message #317041 is a reply to message #316718] Tue, 29 April 2008 05:12 Go to previous messageGo to next message
suresh_oradba
Messages: 5
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
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]

Report message to a moderator

Re: A function to replace CAPS [message #318106 is a reply to message #317053] Mon, 05 May 2008 11:39 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
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 Go to previous messageGo to next message
anna marie
Messages: 26
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: 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 Go to previous message
Frank
Messages: 7880
Registered: March 2000
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
Previous Topic: Reg tablespaces
Next Topic: How to create an autonomous tranaction to solve the mutation error
Goto Forum:
  


Current Time: Sat Dec 10 01:14:51 CST 2016

Total time taken to generate the page: 0.14756 seconds