Home » SQL & PL/SQL » SQL & PL/SQL » column format
icon6.gif  column format [message #233899] Sat, 28 April 2007 00:46 Go to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

HI,



I want to display the values of a column which contains
the name(may have any number of words) of persons
in the format
firstletter of firs word.first letter of second word.first letter of*****.first letter of lastword
in sql . is it possible

to make it understandable i am giving an example as below

i have an table samar

SQL> desc samar;
Name Null? Type
----------------------------------------- -------- ---------------
NAME VARCHAR2(30)

which contains the names as,


SQL> select * from samar;

NAME
----------------------------
Samarendra Mishra
Amarendra kumar mishra
jitendra kumar mishra
gyanendra kumar mishra
Rajendra Kumar Mishra
CLARK
MILLER

now i want select rows with the name format as

S.M
A.k.m
j.k.m
g.k.m
like wise...

irespective of their no. of words IN SQL . IS IT POSSIBLE?

THANKS & REGARDS...
FROM
SAMARENDRA MISHRA
Re: column format [message #233969 is a reply to message #233899] Sat, 28 April 2007 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use NAME as a column name, it is reserved by Oracle and may not work in a future version.

SQL> select replace(trim(regexp_replace(val||' ','([^ ])[^ ]* ','\1 ')),' ','.') v from t;
V
----------
S.M
A.k.m
j.k.m
g.k.m
R.K.M
C
M

7 rows selected.

Regards
Michel

[Updated on: Sat, 28 April 2007 10:26]

Report message to a moderator

Re: column format [message #233995 is a reply to message #233899] Sat, 28 April 2007 14:53 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Here's another way.

create table t ( name varchar2( 30 ) );

insert into t values ( 'Samarendra Mishra'       );
insert into t values ( 'Amarendra kumar mishra'  );
insert into t values ( 'jitendra kumar mishra'   );
insert into t values ( 'gyanendra kumar mishra'  );
insert into t values ( 'Rajendra Kumar Mishra'   );
insert into t values ( 'CLARK'                   );
insert into t values ( 'MILLER'                  );

commit

column initials format a10

select
  rtrim( regexp_replace( name, '([^ ])[^ ]* *', '\1.' ), '.' ) initials
from t ;

INITIALS
----------
S.M
A.k.m
j.k.m
g.k.m
R.K.M
C
M

--
Joe Fuda
http://www.sqlsnippets.com/
icon6.gif  Re: column format [message #234091 is a reply to message #233899] Sun, 29 April 2007 23:27 Go to previous messageGo to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

hi michel ,

thanks for your reply ...

1>but can i do it in oracle 8i, i am using version 8.1.7



2>can you tell me the details about the regexp_replace(val||' ','([^ ])[^ ]* ','\1 ',1,0)),' ','.') function .

thanks & REGARDS

SAMAR



Re: column format [message #234095 is a reply to message #234091] Mon, 30 April 2007 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is why you MUST give your version in all your posts.
2/ I will explain Joe's regexp solution which is better: regexp_replace( val, '([^ ])[^ ]* *', '\1.' ).

([^ ]): the parenthesis () indicate to memorize what will be selected inside them. The brackets [] indicate a list of characters that can fit the selection condition, as this list starts with ^ this means that we are interested by all but those in the list, the list is here just a space. So this expression asks Oracle to memorize the first non space character.
[^ ]*: we already see that [^ ] means all non space character, * means any number of characters, even none. So the expression means all non space characters (after the first one taken be the previous expression).
*: means all space characters following.
So '([^ ])[^ ]* *' means: memorize the first non space character and skip all non space characters followed by all space characters till anoher non space character or the end.
This expression must be replaced by '\1.'. \1 is the first memorized expression (here the first non space character).

In summary, Oracle searches all patterns consisting in any number of non space characters followed by any number of spaces and replace them by their first character and add a dot.

If I have time I will search for a 8i solution.

Regards
Michel

[edit: add a missing word]

[Updated on: Mon, 30 April 2007 10:35]

Report message to a moderator

Re: column format [message #234245 is a reply to message #234091] Mon, 30 April 2007 10:21 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
If you can safely assume a given maximum number of initials in the end result then the following solution may work on 8i. In it we assume that NAME will contain no more than 1 first name, 3 middle names, and one last name in it.

create table t ( name varchar2( 30 ) );

insert into t values ( 'Samarendra Mishra'       );
insert into t values ( 'Amarendra kumar mishra'  );
insert into t values ( 'jitendra kumar mishra'   );
insert into t values ( 'gyanendra kumar mishra'  );
insert into t values ( 'Rajendra Kumar Mishra'   );
insert into t values ( 'CLARK'                   );
insert into t values ( 'MILLER'                  );

commit

create table i ( occurrence integer );
insert into i values ( 1 );
insert into i values ( 2 );
insert into i values ( 3 );
insert into i values ( 4 );
insert into i values ( 5 );

commit;


column initials format a10
column name format a30

select
  t2.name ,
  max( decode( t2.occurrence * length( letter ), 1, letter, null ) ) ||
    max( decode( t2.occurrence * length( letter ), 2, '.'||letter, null ) ) ||
    max( decode( t2.occurrence * length( letter ), 3, '.'||letter, null ) ) ||
    max( decode( t2.occurrence * length( letter ), 4, '.'||letter, null ) ) ||
    max( decode( t2.occurrence * length( letter ), 5, '.'||letter, null ) )
    as initials
from
  (
    select
      t.name ,
      i.occurrence ,
      substr
      ( name,
        decode
        ( instr( ' ' || name, ' ', 1, i.occurrence ),
          0, null,
          instr( ' ' || name, ' ', 1, i.occurrence )
        ) ,
        1
       ) as letter
    from
      t, i
  ) t2
group by
  t2.name
;

NAME                           INITIALS
------------------------------ ----------
CLARK                          C
MILLER                         M
Amarendra kumar mishra         A.k.m
gyanendra kumar mishra         g.k.m
jitendra kumar mishra          j.k.m
Rajendra Kumar Mishra          R.K.M
Samarendra Mishra              S.M

drop table t ;
drop table i ;


To understand the solution, try looking at the output from individual components. Start with the inline view and then work your way out from there.

--
Joe Fuda
http://www.sqlsnippets.com/
Re: column format [message #234256 is a reply to message #234245] Mon, 30 April 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Neat! Thumbs Up

Table i can be replace by:
SQL> select rownum occurrence from all_objects where rownum<=5;
OCCURRENCE
----------
         1
         2
         3
         4
         5

5 rows selected.

Regards
Michel
icon6.gif  Re: column format [message #234339 is a reply to message #234256] Mon, 30 April 2007 23:55 Go to previous messageGo to next message
samarendra_81
Messages: 10
Registered: April 2007
Location: india
Junior Member

great joe ,

do you have the code before? or you make it after getting my questions. Cool

any way one thing it works butif....

we will give more than one spaces in the middle name and in the starting and ending thenn..

just look at below

SQL>

NAME INITIALS
------------------------------ ---------
sameer kumar .s.k. . <--dots from start?
Amarendra kumar mishra A.k.m and end
CLARK C
MILLER M
Rajendra Kumar Mishra R.K. . .M <--more dots?
Samarendra Mishra S.M
gyanendra kumar mishra g. . .k.m
jitendra kumar mishra j.k. .m



thats the problem

ok again thanks very much for your time given

with a warm regards ...

from
samarendra mishra

Re: column format [message #234344 is a reply to message #234339] Tue, 01 May 2007 00:26 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
samarendra,

Have a look at thread "Replacing Multiple Blanks by single blanks in text" on AskTom.

Regards
Michel
Previous Topic: What is LMS?
Next Topic: problem while sending mail thru oracle --
Goto Forum:
  


Current Time: Tue Dec 06 00:26:26 CST 2016

Total time taken to generate the page: 0.07832 seconds