CODEWITHSUNDEEP
mysqlsqloracle-databasesubstring
O P
O P

Reputation: 2365

Substring from last index

ABC:123 UVW XYZ NN-000

What is the best method to get the value after the last space using substr()? In this case I want to get NN-000 but also be able to get that last value in the case that it's greater than or less than 6 characters.

Upvotes: 0

Views: 2973

Answers (3)

Siva Charan
Siva Charan

Reputation: 18064

In Oracle, use SUBSTR and INSTR functions

SELECT SUBSTR('ABC:123 UVW XYZ NN-000', INSTR('ABC:123 UVW XYZ NN-000', ' ', -1)) 
AS LASTOCCUR
FROM DUAL

RESULT:

| LASTOCCUR |
-------------
|    NN-000 |

Refer LIVE DEMO

Upvotes: 4

Taryn
Taryn

Reputation: 247720

In MySQL you could use reverse and substring_index:

select data, 
  rv, 
  reverse(substring_index(rv, ' ', 1)) yd
from
(
  select data,
    reverse(data) rv
  from yt
) d;

See SQL Fiddle with Demo

In Oracle you could use reverse, substr and instr:

select data, 
  reverse(substr(rv, 0, instr(rv, ' '))) rv
from
(
  select data, reverse(data) rv
  from yt
) d

See SQL Fiddle with Demo

Upvotes: 2

Hart CO
Hart CO

Reputation: 34774

Combine the powers of RIGHT(),REVERSE() AND LOCATE()

SELECT RIGHT('ABC:123 UVW XYZ NN-000',LOCATE(' ',REVERSE('ABC:123 UVW XYZ NN-000'))-1)

EDIT: Locate in MYSQL, not CHARINDEX

REVERSE() reverses the string, so that the 'first' space it finds is really the last one. You could use SUBSTRING instead of RIGHT, but if what you're after is at the end of the string, might as well use RIGHT.

Upvotes: 1

Related Questions