5 Replies Latest reply: Feb 28, 2013 6:11 AM by 367852 RSS

    Trim or Regexp?

    976439
      Hi, i am just trying to work something regarding removing the first characters of a text string based on a condition. i have a column with various text strings, and I have noticed some text fields have 4 letters at the start that I would like to remove i.e. AN01 and SA01 but i am not too sure if the sql code should be written using the TRIM function or REGEXP_REPLACE so just wanted to find out what the correct method would be. If somebody could please advise.

      Thanks in advance.
        • 1. Re: Trim or Regexp?
          ranit B
          Use the plain old SUBSTR
          ranit@XE11GR2>> with xx as(
            2  select 'ranit' w from dual union all
            3  select 'oracle' w from dual UNION ALL
            4  select 'SN01aaa' w from dual UNION ALL
            5  select 'AS01bbb' w from dual
            6  )
            7  select
            8  case when w like 'SN01%' or w like 'AS01%'
            9  then
           10  SUBSTR(w, 4, LENGTH(w))
           11  else
           12  w
           13  end case
           14  from xx;
          
          CASE
          ----------------
          ranit
          oracle
          1aaa
          1bbb
          
          Elapsed: 00:00:00.03
          • 2. Re: Trim or Regexp?
            636309
            Hi,
            If you don't care about occurences of the undesired text in places other than the beginning, then you could do something like this.
            WITH sample AS 
            (select 'AN01333' column1 from dual 
              union all 
             select 'SA01999' column1 from dual)
            SELECT decode(substr(column1, 1, 4), 'AN01', replace(column1, 'AN01'), 'SA01', replace(column1, 'SA01'), column1)
            FROM   sample
            • 3. Re: Trim or Regexp?
              Frank Kulash
              Hi,
              973436 wrote:
              Hi, i am just trying to work something regarding removing the first characters of a text string based on a condition. i have a column with various text strings, and I have noticed some text fields have 4 letters at the start that I would like to remove i.e. AN01 and SA01
              Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
              Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
              Always say which version of Oracle you're using (for example, 11.2.0.2.0).
              See the forum FAQ {message:id=9360002}
              but i am not too sure if the sql code should be written using the TRIM function or REGEXP_REPLACE so just wanted to find out what the correct method would be. If somebody could please advise.
              There are many different string manipulation functions that might help here.

              TRIM is one of the least likely. TRIM can only remove a single characrter, such as 'A' or 'N'. LTRIM can remove any of a set of characters, such as the set of 4 characters 'A', 'N', '0' and '1', but it pays no attentiton how many of them are found, or in what order, so LTRIM ('NANAfubar', 'AN01') returns 'fubar', which (I'm guessing) is not what you want.

              REGEXP_REPLACE is more likely to help, because it is so powerful. All that power has a price, however, and there may be some other function (or something like a CASE expression) that can do what you want perfectly well, and do it more efficiently than REGEXP_REPLACE.
              • 4. Re: Trim or Regexp?
                976439
                Frank, thanks for your comments, I appreciate no matter what situation, i need to specify my tables, outcomes required in order for someone to help me with my problem. Thnx once again
                • 5. Re: Trim or Regexp?
                  367852
                  There are many approaches, see which one is best for you.
                  SQL> with test_data as (
                    2  SELECT 'AN01QWER' col1 FROM DUAL UNION ALL
                    3  SELECT 'SA01ASDF' FROM DUAL UNION ALL
                    4  SELECT 'AN88QWER' FROM DUAL UNION ALL
                    5  SELECT 'SA88ASDF' FROM DUAL)
                    6  SELECT case when col1 like ('AN01%') OR col1 like ('SA01%') then
                    7                   replace(t.col1,substr(t.col1,1,4))
                    8         else
                    9             col1
                   10         end col1
                   11  FROM test_data t
                   12
                  SQL>
                  SQL> /
                  
                  COL1
                  --------
                  QWER
                  ASDF
                  AN88QWER
                  SA88ASDF