4 Replies Latest reply: Sep 17, 2010 9:58 AM by AlanWms RSS

    Oracle String Function - trim or REGEXP_REPLACE or substr

    Lucy Discover
      I know this question could be so basic. I am just supposed to Update a column that looks like

      *KA12345
      *KA 12345

      with just the 5 numeric digits found at the end. some of the Data in the column has Space inbetween the STRING. I want to truncate the space and update it with somthing like substr(stringname,3,5) ... So what do i do to truncate Space in the data. I tried (trim(chr(32) from string_name and it did not return the desired results.
        • 1. Re: Oracle String Function - trim or REGEXP_REPLACE or substr
          Frank Kulash
          Hi,
          Lucy Discover wrote:
          I know this question could be so basic. I am just supposed to Update a column that looks like

          *KA12345
          *KA 12345

          with just the 5 numeric digits found at the end. some of the Data in the column has Space inbetween the STRING. I want to truncate the space and update it with somthing like substr(stringname,3,5) ... So what do i do to truncate Space in the data. I tried (trim(chr(32) from string_name and it did not return the desired results.
          Sorry, it's unclear what you're asking.

          To get the last 5 characters of stringname:
          SUBSTR ( stringname
                 , -5
                 )
          To get the group of digits at the end of the string, regardless of how many there are:
          REGEXP_SUBSTR ( stringname
                     , '[0-9]+$'
                     )
          Whenever you have a problem, it helps if you post a little sample data (CREATE TABLE and INSERT statments) and the results you want from that data.
          • 2. Re: Oracle String Function - trim or REGEXP_REPLACE or substr
            BluShadow
            If you know it's always the last 5 characters of the string you could use:
            SQL> ed
            Wrote file afiedt.buf
            
              1  with t as (select '*KA12345' as txt from dual union all
              2             select '*KA 12345' from dual)
              3  --
              4  select substr(txt,-5) as num
              5* from t
            SQL> /
            
            NUM
            -----
            12345
            12345
            
            SQL>
            If you want to remove spaces you can use:
            SQL> ed
            Wrote file afiedt.buf
            
              1  with t as (select '*KA12345' as txt from dual union all
              2             select '*KA 12345' from dual)
              3  --
              4  select replace(txt,' ') as space_free
              5* from t
            SQL> /
            
            SPACE_FRE
            ---------
            *KA12345
            *KA12345
            
            SQL>
            • 3. Re: Oracle String Function - trim or REGEXP_REPLACE or substr
              AlanWms
              with data as
              (
              select '*KA12345' fld from dual union all
              select '*KA 12345' from dual union all
              select '*KA 123 45' from dual
              )
              select fld, regexp_replace(fld,'[ ]+') result
              from data;
              --- FLD         RESULT
              --- *KA12345    *KA12345
              --- *KA 12345   *KA12345
              --- *KA 123 45  *KA12345
              • 4. Re: Oracle String Function - trim or REGEXP_REPLACE or substr
                Lucy Discover
                Thank you Frank .. Thank you all ...