CODEWITHSUNDEEP
sqldatabaseoracle-database
Adam Neighbors
Adam Neighbors

Reputation: 15

Oracle Select specific string from table plus following characters

I want to select all rows from a table that contain a specific string, but then also return the following 4 characters after it that will usually be dynamic.

So basically I've got a table that looks like this

Column1 | Column2

CSE-001 | jdaosfjodas

CSE-002 | fjdaosfjdosj

GDS-001 | dskfjaodij

CSE-103 | fojdsiofj

Now, I want to Select all rows that start with CSE as well as the "-" and subsiquent three numbers that will follow, but those three numbers could be anything. So, based off the table above I'd want to return CSE-001, CSE-002, and CSE-103.

Sorry if this question is done horribly I'm relatively new to the site. Thanks!

EDIT

@GordonLinoff Said almost exactly what I want, but what about instances where Column1 returns something like 

Column1 | Column 2
GDE-002, CSE-101, GDE-003 | faosfd

In this case I would want to return that specific row, but only the "CSE-101" part of it.

Upvotes: 0

Views: 78

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done using regexp_like.

For case-insensitive match use,

select * from tbl
where regexp_like(column1,'^CSE-[0-9]{3}$','i') 
--this query matches values like csE-101,CsE-001,cse-290 etc.

If you need case-sensitive match, use

select * from tbl
where regexp_like(column1,'^CSE-[0-9]{3}$')

Edit: Based on OP's edit after posting the answer above,

select * from tbl
where regexp_like(column1,'(^|\s|,)CSE-[0-9]{3}(,|\s|$)')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

It sounds like you want something like this:

select substr(col1, 1, 7), t.*
from t
where t.col1 like 'CSE-___%';

Upvotes: 0

Related Questions