This discussion is archived
4 Replies Latest reply: Sep 29, 2013 11:25 AM by Nick K. RSS

sql to substr firstname and last name

adf009 Explorer
Currently Being Moderated

hi how can i subtr first name and last name

 

 

select first_name + lastname from employee

 

for example if i have james michael i what to have JMichael

  • 1. Re: sql to substr firstname and last name
    Etbin Guru
    Currently Being Moderated

    Maybe

     

    select upper(substr(one,1,1)) || initcap(two) result

      from (select 'james' one,'michael' two

              from dual

           )

    or


    select upper(substr(one,1,1)) || initcap(substr(one,instr(one,' ') + 1)) result

      from (select 'james michael' one

              from dual

           )

     

    RESULT
    JMichael

     

    Regards

     

    Etbin

  • 2. Re: sql to substr firstname and last name
    adf009 Explorer
    Currently Being Moderated

    what if i have 500 user in my table,is there no other way to do it without hard coded

    i don't what to user

    select 'james michael' one

              from dual


    i what to use my table field firstname,lastnmae

  • 3. Re: sql to substr firstname and last name
    Etbin Guru
    Currently Being Moderated

    select from your table instead of dual. We don't have your tables and you didn't post any samples, expected results, etc. ...

     

    select upper(substr(one,1,1)) || initcap(substr(one,instr(one,' ') + 1)) result

      from (select job || ' ' || ename one

              from emp

           )

     

    RESULT
    PKing
    MBlake
    MClark
    MJones
    AScott
    AFord
    CSmith
    SAllen
    SWard
    SMartin
    STurner
    CAdams
    CJames
    CMiller

     

    Regards

     

    Etbin

     

    Message was edited by: Etbin emp example

  • 4. Re: sql to substr firstname and last name
    Nick K. Newbie
    Currently Being Moderated

    As another approach , you could use regexp_replace() regular expression function:

     

    /* sample of data */

    with t1(col) as(

      select 'james michael' from dual union all

      select 'john smith'       from dual union all

      select 'tom ross'         from dual

    )

    select regexp_replace(initcap(trim(col)), '(.)(.*\s)(.*$)', '\1\3') as res

      from t1

     

     

    Result:

    --------------------

    JMichael
    JSmith
    TRoss

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points