CODEWITHSUNDEEP
sqloracle-databaseselectoracle11goracle10g
user2428207
user2428207

Reputation: 825

Instr - Last index of last character

I'm using an Oracle 11g DB and I want to find the position of the last character of a string.

For example:

"City=Amsterdam"

The string in this case would be "City=", I would like to get the position of the "=". I know INSTR(Input,'City=', 1, 1)+4, would kinda work but I am looking for another way.

edit: Basically I want to use a substr function to extract "Amsterdam". But the select statement should be as clean as possible.

What I forgot to mention, the string contains more than "City=Amsterdam", it contains also "Customer=124"

"City=Amsterdam"; "Customer=124"

Upvotes: 3

Views: 9713

Answers (2)

MrSimpleMind
MrSimpleMind

Reputation: 8597

INSTR('City=Amsterdam', '=', -1)

use -1 for start pos!

from your comment, answer 2

select SUBSTR( input, INSTR(input, '=', -1)+1 ) as city 
  from yourTable;

And if you have more fields, before or after, as you mentions with customer=... you can do:

select substr(input, 
              INSTR(input,'City=')+5, 
              INSTR(input,'"', INSTR(input,'City='))-INSTR(input,'City=')-5 
             ) as city from city;

And some kind of "fancy" query, to comment and make it more flexible with other fields...

select substr(c.input, c.citystart, c.cityend - c.citystart) as city
from (
select input as input, 
       INSTR(input,'City=')+5 as citystart, 
       INSTR(input,'"', INSTR(input,'City=')) as cityend
from city) c;

Test data:

create table city (input char(64));

insert into city values('"City=Amsterdam"; "Customer=124"');

insert into city values('"Customer=11"; "City=USA"');

insert into city values('"Tel=+00"; "City=China"; "Customer=1"');

insert into city values('"Tel=+00"; "Post=11111";  "Customer=333"; "City=Canada"');

See updated SQLFIDDLE:

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52386

Wouldn't you actually use:

INSTR(Input,'City=')+ length('City=') - 1

This seems to be the optimal code.

Upvotes: 0

Related Questions