MySQL REGEXP in where not finding expected results

Question

I have this query:

SELECT certREF,CAST(SUBSTRING_INDEX(certREF,'-',-1) as UNSIGNED) as REF 
FROM certificatecatalog 
WHERE certREF is not null AND `certREF` REGEXP 'F[\d]+-[\d]+' 
ORDER BY REF DESC 
Limit 0,1

I have data rows in column certREF like:

F17-1257
F17-3546
F18-8854
F19-9854

I want to be able to pull the highest number after "Fnumber-"

The regualar expression seems to work when i put it in one of those live regex testers.

However i'm getting an empty result set.

If someone could let me know where i'm going wrong :)

Thanks

Use REGEXP '^F[0-9]+-[0-9]+$' (see demo).

Wiktor Stribiżew
– Wiktor Stribiżew

2018-09-26 15:47:27 +00:00
Commented Sep 26, 2018 at 15:47 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Sep 26, 2018 at 15:47

Gordon Linoff · Accepted Answer · 2018-09-26 15:50:24Z

1

I think the simplest way is implicit conversion:

select max(substr(certRef, 2) + 0)
from certificatecatalog
where certRef like 'F%';

MySQL will convert the digits after the first F to a number, stopping at the first non-digit. It then returns the maximum value. Regular expressions are not needed for this.

EDIT:

If you want the part after the hyphen, then you can do either:

select max(substr(certRef, 5) + 0)
from certificatecatalog
where certRef like 'F%';

or:

select max(substring_index(certRef, '-', -1) + 0)
from certificatecatalog
where certRef like 'F%';

edited Sep 26, 2018 at 15:50

answered Sep 26, 2018 at 15:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

JimmyUK1 Over a year ago

Hi, Sorry i perhaps needed to expand on the data in the table more, there are some with FE4-99, FR15-55, it's just the F on it's own i need.

Barbaros Özhan · Accepted Answer · 2018-09-26 16:01:53Z

0

You might use substr() with instr() function as :

select max(substr(certREF,instr(certREF,'-')+1,length(certREF)))
       as maxNumber
  from certificatecatalog;

MAXNUMBER
  9854

SQL Fiddle Demo

answered Sep 26, 2018 at 16:01

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

MySQL REGEXP in where not finding expected results

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related