CODEWITHSUNDEEP
regexoracle-databaseoracle11goracle10goracle-sqldeveloper
user3146332
user3146332

Reputation: 51

How can i extract a specific part of string in oracle?

I have some records with a column name "text_note". Now i need to find out that which text_note contains a string like %CTOE0%.

I think it can be done using regular expressions,but since i'm not that much familiar with regular expressions, i'm not able to create a regular expression for it.

Please help.
FORGOT TO MENTION i WANT THE CODE(CTOE) PART IN A DIFFERENT COULMN

my issue is that i just don't wanna show 'CTOE' in next column.I want to know what code is used. the types of code that can be used are CTOE01,CTOE02,CTOE03 AND SO ON.Now i wanna know which of these codes have been used.

The example is as follows: if the text not contains "dsghdsCTOE01dbhf" then new column should contain "CTOE01" if the text not contains "dsCTOE02dbhf" then new column should contain "CTOE02" –

Upvotes: 2

Views: 3713

Answers (3)

wolφi
wolφi

Reputation: 8361

I'd use regexp_substr for that:

CREATE TABLE t (text_note VARCHAR2(30));
INSERT INTO t VALUES ('dsghdsCTOE01dbhf');
INSERT INTO t VALUES ('dsCTOE02dbhf');
INSERT INTO t VALUES ('soemthingelse');

SELECT text_note, 
       regexp_substr(text_note, 'CTOE[0-9]+') AS code 
  FROM t
 WHERE   regexp_like(text_note, 'CTOE[0-9]+');


text_note         code
----------------  ----
dsghdsCTOE01dbhf  CTOE01
dsCTOE02dbhf      CTOE02

The regexp_substr extracts your new column, and the regexp_like filters rows that contain CTOE....

Upvotes: 2

ngrashia
ngrashia

Reputation: 9904

EDIT:

SELECT text_note, 
substr(text_note, (instr(text_note, 'CTOE0')), 6)   AS CODE
FROM table where text_note like '%CTOE0%';

In above query, I assume that your code will be just 6 characters long and using substr function to capture the 6 character code. The ouptut required is got with above query.

ORIGINAL ANSWER:

Check out if this would help you! 

Select text_note, 'CTOE' CODE from table
where text_note like '%\%CTOE0\%%;

I assume that % is also a part of your search string and trying to escape percentage as given here

Upvotes: 0

zx81
zx81

Reputation: 41838

If you mean that the field starts and ends with a % and only contains upper-case letters and digits:

SELECT * FROM yourtable
WHERE REGEXP_LIKE(text_note, '^%[A-Z0-9]+%$', 'c');

Upvotes: 0

Related Questions