4 Replies Latest reply: Sep 29, 2013 1:25 PM by Nick K. RSS

    sql to substr firstname and last name

    Tshifhiwa

      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

          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
            Tshifhiwa

            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

              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.

                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