CODEWITHSUNDEEP
sqloracle-database
Newbie
Newbie

Reputation: 35

Oracle Substr SQL

I got list of database.

**NAME**
OEE_LINE_ABC
OEE_LINE_ABCD
OEE_LINE_QWERTYUIOP
OEE_LINE_zxcvbd

I need to grab the Name that after OEE_LINE_. But it does not standardize how many characters. What I get from google is fix how many characters.

Example :

SUBSTR(Name,10,3)
Result: 'ABC'

If I use above substr, it able to grab the 1st value (OEE_LINE_ABC) without problem, when comes to 2nd value, there's a problem because above substr only can retrieve 3 characters.

Expected result:

the SQL statement able to grab values that after OEE_LINE_

Result(return name without the OEE_LINE_):

Name = ABC
Name = ABCD
Name = QWERTYUIOP
Name = zxcvbd

Any related post to share and answer?

Upvotes: 1

Views: 257

Answers (3)

Abecee
Abecee

Reputation: 2393

If you need the rest of the string, just specify the start position:

SELECT SUBSTR(name, 10)

SQL Fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I would suggest looking for the last '_' and taking the string from there:

select substr(name, length(name) - instr(reverse(name), '_') + 2)

Upvotes: 2

void
void

Reputation: 7890

SUBSTR(Name,10,length(Name)-9)

Upvotes: 1

Related Questions