This discussion is archived
5 Replies Latest reply: Feb 28, 2013 4:11 AM by 367852 RSS

Trim or Regexp?

976439 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

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