Get part of string in mysql

Question

I need get part of a string in MySQL, to put it inside a trigger where this part of a string is a key for another table and will be use as foreign key

The string looks like: P####PR#### where # is a number, after 'PR' the numbers are an ID for another table where a I need save, but... the size of the number parts is variable like

P1PR20
P20PR321
P4321PR54321
P1PR1

I need remove all characters of this string before do last numeric part

P1PR20        -> 20
P20PR321      -> 321
P4321PR54321  -> 54321
P1PR1         -> 1

please, any suggestion?

You can get a substring from the last index of 'PR' to the end of the string. Unfortunately I can't put up a full answer but I'm sure someone else will, or you can google something like "MySQL SUBSTRING function" — Brian Driscoll
– Brian Driscoll, Commented Oct 10, 2014 at 13:48

Joe Taras · Accepted Answer · 2014-10-10 13:53:59Z

9

I suppose you have a pattern P####PR#### (as you said in your question), so

Try this:

SELECT SUBSTRING(field, INSTR(field, 'PR') + 2, 100)
FROM dual

EDIT

INSTR(field, 'PR') <-- get the index of first occurence of PR. In your cases:

If you have:

P1PR20 the value of function is 3

P20PR321 the value of function is 4.

I add 2 because I must to position on the first char after PR.

In the first case:

You have SUBSTRING(field, 3 + 2, 100) --> 20

In the second case:

You have SUBSTRING(field, 4 + 2, 100) --> 321

100 is a generic offset

edited Oct 10, 2014 at 13:53

answered Oct 10, 2014 at 13:48

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matheus Hernandes Over a year ago

It's almost this, but the size of numbers before PR is variable too, it can be P20PR20 OR P321PR20

Joe Taras Over a year ago

Yes, but I take the index of PR, now update my answer to better explain my strategy

Collectives™ on Stack Overflow

Get part of string in mysql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related