DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: How to get the substring value from a column with data?

  1. #1
    Join Date
    Mar 2002
    Posts
    38

    How to get the substring value from a column with data?

    Hi Friends,
    How to get the substring value from a column with data?

    Example
    -------

    Names Initials
    ----- --------
    MTOLISI
    FERNANDO
    MAKGATLISHA SWARTBOOI
    SIVENGI
    T A
    SAMANTHA MARY

    I need to populate the initials column by taking the first character from the Names field
    My Initials column should be as below

    Initials
    --------
    M
    F
    MS
    S
    T A
    SM

    Thanks Guys,
    Kishan
    SUROOP B

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    select substr(names,1,1) initials from tablename

  3. #3
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    SELECT
    substr(fullnames,1,1)||substr(fullnames,instr(fullnames,' ')+1,1) initials
    FROM
    names

  4. #4
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    select substr(names,1,1) || substr(names, instr(names,' ')+1,1) from tablename;

    You might want to add a decode before the second concatenation to check if there is a space.

  5. #5
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    Decode not necessary. Who cares?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    translate(initcap(column),
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
    Will deal with any number of names - but I feel there must be a neater way . . .
    Last edited by DaPi; 04-15-2004 at 11:48 AM.

  7. #7
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    DaPi, you so sneaky!
    I might use this trick somewhere.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Watch out for names like "Hervé Niño 13th" it'll need some adjusting!

  9. #9
    Join Date
    Feb 2004
    Posts
    77
    Or maybe just use the upper to make everything uppercase.

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by oracle_faq
    Or maybe just use the upper to make everything uppercase.
    How would that help?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width