CODEWITHSUNDEEP
mysql
pks
pks

Reputation: 21

MySQL sub_string search (prefix ID's)

I have location ID's with two letter location prefix followed by a number

(AB1, AB2. CA1, CA2, CD1)

that will sequentially increment by 1 each time a new record is added against a given location. To find the last added ID matching the prefix I'm using a mysql substring .. Its something like this ...

SELECT MAX( SUBSTRING( `locationID` , 3, 11 ) )
FROM `items`
WHERE LEFT( `locationID` , 2 ) = "AL"

It doesn't work (it would only pick up numbers under 10) Can someone please help me with this ?

Upvotes: 0

Views: 277

Answers (1)

zerkms
zerkms

Reputation: 254916

You need to cast it to number:

SELECT MAX(CAST(SUBSTRING( locationID , 3, 11 ) AS UNSIGNED))

Upvotes: 1

Related Questions