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
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
973436 wrote: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.
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.There are many different string manipulation functions that might help here.
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