Home » SQL & PL/SQL » SQL & PL/SQL » how to insert spaces between (oracle 9i)
how to insert spaces between [message #361411] Wed, 26 November 2008 05:35 Go to next message
prerana_pant@rediffmail.c
Messages: 11
Registered: November 2008
Junior Member
Pls tell how to insert spaces between
Mrs.MadhuraDeshpande

to seperated Title, First name, Last name
This data is in one single column.
Re: how to insert spaces between [message #361413 is a reply to message #361411] Wed, 26 November 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define Title, First Name and Last name.
What criteria to use to separate them?

Regards
Michel
Re: how to insert spaces between [message #361419 is a reply to message #361411] Wed, 26 November 2008 05:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Oracle (Any database) is no intelligent to understand title, First name and Last name without any specification or definition.

There should be some way of seperation between them.

Smile
Rajuvan.

Re: how to insert spaces between [message #361420 is a reply to message #361411] Wed, 26 November 2008 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the criterium is Capital then you can use something like:
SQL> select regexp_replace('Mrs.MadhuraDeshpande','([^^])([A-Z])','\1 \2') name from dual;
NAME
----------------------
Mrs. Madhura Deshpande

1 row selected.

Regards
Michel
Re: how to insert spaces between [message #361421 is a reply to message #361411] Wed, 26 November 2008 06:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't know whether it is a sheer coinidence a similar issue has been posted here (but with some more information) and the infamous keyword "urgent".

http://forums.oracle.com/forums/thread.jspa?threadID=831200&tstart=0

Regards

Raj
Re: how to insert spaces between [message #361422 is a reply to message #361411] Wed, 26 November 2008 06:15 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
select regexp_replace('Mrs.MadhuraDeshpande','([^^])([A-Z])','\1 \2') name from dual;


Wrong ( for OP )

Quote:
Home SQL & PL/SQL SQL & PL/SQL how to insert spaces between (oracle 9i)


Smile
Rajuvan.
Re: how to insert spaces between [message #361423 is a reply to message #361420] Wed, 26 November 2008 06:15 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Another alternative.

select regexp_replace('Mr.NavneetSharma','([A-Z].)',' \1') from dual


Regards,
Navneet
Re: how to insert spaces between [message #361424 is a reply to message #361420] Wed, 26 November 2008 06:16 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,

Michel Cadot wrote on Wed, 26 November 2008 17:30
If the criterium is Capital then you can use something like:
SQL> select regexp_replace('Mrs.MadhuraDeshpande','([^^])([A-Z])','\1 \2') name from dual;
NAME
----------------------
Mrs. Madhura Deshpande

1 row selected.

Regards
Michel



I don't think the solution will work with OP's version (Oracle 9i). Even I had the same thought first but dropped the thought seeing the version.

Regards,
Jo
Re: how to insert spaces between [message #361426 is a reply to message #361424] Wed, 26 November 2008 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually you are right, I didn't see his version, my apologies.

@navneet_sharman,
your solution does not work:
SQL> select regexp_replace('Mr.NavneetSharma','([A-Z].)',' \1') from dual
  2  /
REGEXP_REPLACE('MR.
-------------------
 Mr. Navneet Sharma

1 row selected.

It adds a space at first place.

Regards
Michel
Re: how to insert spaces between [message #361430 is a reply to message #361424] Wed, 26 November 2008 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This one works in 9i:
SQL> with 
  2    data as (
  3      select 'Mrs.MadhuraDeshpande' name from dual
  4    ),
  5    letters as (
  6      select level id, length(name) maxid,
  7             case -- Add a space before Capital
  8               when level > 1 and 
  9                    substr(name,level,1) between 'A' and 'Z'
 10                 then ' '
 11             end||substr(name,level,1) letter 
 12      from data
 13      connect by level <= length(name)
 14    )
 15  select replace(sys_connect_by_path(letter,'/'),'/','') name
 16  from letters
 17  where id = maxid
 18  connect by prior id = id-1
 19  start with id = 1
 20  /
NAME
----------------------------------------------------------------
Mrs. Madhura Deshpande

1 row selected.

Regards
Michel
Re: how to insert spaces between [message #361439 is a reply to message #361411] Wed, 26 November 2008 07:19 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,

That was a good solution. I was breaking my head on some logic and developed this horrible looking code:
SQL> WITH test_tab AS
  2       (SELECT 'Mr.JamesBond' col_1
  3          FROM DUAL
  4        UNION ALL
  5        SELECT 'PremKumar'
  6          FROM DUAL
  7        UNION ALL
  8        SELECT 'Dr.AbdulKalam'
  9          FROM DUAL
 10        UNION ALL
 11        SELECT 'Mrs.MadhuraDeshpande'
 12          FROM DUAL)
 13  SELECT col_1 from test_tab;

COL_1
--------------------
Mr.JamesBond
PremKumar
Dr.AbdulKalam
Mrs.MadhuraDeshpande

SQL> WITH test_tab AS
  2       (SELECT 'Mr.JamesBond' col_1
  3          FROM DUAL
  4        UNION ALL
  5        SELECT 'PremKumar'
  6          FROM DUAL
  7        UNION ALL
  8        SELECT 'Dr.AbdulKalam'
  9          FROM DUAL
 10        UNION ALL
 11        SELECT 'Mrs.MadhuraDeshpande'
 12          FROM DUAL)
 13  SELECT col_1 Full_Name,
 14         SUBSTR (REPLACE (col_1, '.', '. '),
 15                 1,
 16                 INSTR (REPLACE (col_1, '.', '. '), ' ', 1, 1)
 17                ) title,
 18         SUBSTR (col_1,
 19                   INSTR (col_1, '.', 1, 1)
 20                 + INSTR (TRANSLATE (SUBSTR (col_1, INSTR (col_1, '.', 1, 1) + 1),
 21                                     'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 22                                     '##########################'
 23                                    ),
 24                          '#',
 25                          1,
 26                          1
 27                         ),
 28                   INSTR (TRANSLATE (SUBSTR (col_1, INSTR (col_1, '.', 1, 1) + 1),
 29                                     'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 30                                     '##########################'
 31                                    ),
 32                          '#',
 33                          1,
 34                          2
 35                         )
 36                 - 1
 37                ) first_name,
 38         SUBSTR (col_1,
 39                   INSTR (col_1, '.', 1, 1)
 40                 + INSTR (TRANSLATE (SUBSTR (col_1, INSTR (col_1, '.', 1, 1) + 1),
 41                                     'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
 42                                     '##########################'
 43                                    ),
 44                          '#',
 45                          1,
 46                          2
 47                         )
 48                ) last_name
 49    FROM test_tab;

FULL_NAME            TITLE                   FIRST_NAME           LAST_NAME
-------------------- ----------------------- -------------------- -----------------
Mr.JamesBond         Mr.                     James                Bond
PremKumar                                    Prem                 Kumar
Dr.AbdulKalam        Dr.                     Abdul                Kalam
Mrs.MadhuraDeshpande Mrs.                    Madhura              Deshpande

Assumptions made that names doesn't contain a middle name amd First Name and Last Name are differenciated by Capital Letters.

[***Added***]
Oops.... Misread OP's requirement. I thought OP wanted it as three seperate columns.... Sorry http://img3.mysmiley.net/imgs/smile/mad/mad0228.gif
Regards,
Jo

[Updated on: Wed, 26 November 2008 07:30]

Report message to a moderator

Previous Topic: Oracle StoredProcedure returning Object instead of Cursor
Next Topic: UTL_files
Goto Forum:
  


Current Time: Sun Dec 04 20:24:55 CST 2016

Total time taken to generate the page: 0.13619 seconds