7 Replies Latest reply on Jan 4, 2011 5:03 AM by 803439

    using initcap

    user9093700
      select initcap('hi how are you') from dual

      result: Hi How Are You

      but i need the output as : Hi how are you
        • 1. Re: using initcap
          Efficientoracle
          Hi,
          Then Try this.


          Select Initcap('hi')||' how are you' from dual;
          • 2. Re: using initcap
            Ora
            select initcap('tech')|| 'on the net'
            from dual;

            would return 'Tech on the net'
            • 3. Re: using initcap
              user9093700
              for example i have given such a way but exactly i need to give as

              select name from emp;

              result: david john smith.

              select initcap(name) from emp;

              result: David John Smith

              but i need the output as : David john smith
              • 4. Re: using initcap
                Ora
                You will get the first word with this, and than you can initcap and concat rest.
                SELECT
                SUBSTR(col_name,1,(INSTR(col_name,' ',1,1)-1)) "First Word"
                FROM table_name;

                Edited by: Mahanam on Jan 3, 2011 8:38 PM

                Edited by: Mahanam on Jan 3, 2011 8:47 PM
                • 5. Re: using initcap
                  Billy~Verreynne
                  user9093689 wrote:
                  select initcap('hi how are you') from dual

                  result: Hi How Are You

                  but i need the output as : Hi how are you
                  What rule needs to apply? InitCap rule says that each word in the string needs to have its first character capitalised.

                  If your rule is that the first word in the string only need to start with a caps letter, then you can implement it as follows:
                  select 
                    upper( substr(:string,1,1) ) ||   -- // grab the 1st char and uppercase it
                    substr(:string,2) as "STRING"  -- // add the remainder of the string, unchanged, to it
                  from table
                  There are assumptions with this implementation, like that the 1st character is not a space or punctuation mark and the actual start of the 1st word in the string.
                  • 6. Re: using initcap
                    Ora
                    Here is another way. I have not tested this code, please do it yourself.

                    SELECT replace(initcap(replace(column_name, ' ', '.'), '.', ' ')
                    FROM table_name;
                    1 person found this helpful
                    • 7. Re: using initcap
                      803439
                      WITH t1 AS (SELECT 'david john smith' name FROM dual
                      UNION ALL
                      SELECT 'oracle newbie' FROM dual
                      UNION ALL
                      SELECT 'dummy' FROM dual)
                      SELECT LPad(SubStr(name,2,Length(name)-1),Length(name),Upper(SubStr(name,1,1))) first_cap_name FROM t1;
                      1 person found this helpful