MySQL find the last occurrence of a string pattern in a specific column

Question

I have a mysql table in which there is a column e.g. called name. The column data has a specific pattern nameBase+number. E.g.

name
----------
test0
test1
test2
stack0
stack1
stack2

Each time I want to add data to the column, I have to find the last number for specific nambeBase and add the new entry +number+1.

For example, if now test came, I have to add test3 to db.

My question: What is the best way to 1. check if the nameBase already exists in db(sth like contains) and 2.find the last nameBase number. E.g. here for test is 3.

Update : Everyone, one update. I finally used java Pattern class. So cool and easy. It made everything so simple. I just could add the /d to pattern and then I could check if that matches the name and could use the pattern group to easily access the second part.

Is this for a transactional system where performance is critical? If so, you should probably change the structure to have the name and number in separate columns, with a composite index on them. — Gordon Linoff
– Gordon Linoff, Commented Jul 7, 2012 at 20:44
MySQL + MyISAM has an interesting feature: see dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html / MyISAM Notes. — biziclop
– biziclop, Commented Jul 7, 2012 at 20:47
Thank you but I am not able to change the schema. I have to follow their requirement as it is. — Sara
– Sara, Commented Jul 7, 2012 at 20:54

Michael Berkowski · Accepted Answer · 2012-07-07 21:03:41Z

3

The real solution here is to change the database schema to split this into two columns, the name and its number. It becomes trivial then to get the aggregate MAX() via

SELECT name, MAX(num) AS num FROM tbl GROUP BY name

However,if changing it is not an option, I would recommend using REPLACE() to remove the name portion from the column value leaving only the number portion when querying, and get the aggregate MAX() of that to find the highest existing number for it:

SELECT
  MAX(REPLACE(name, <the name to search>, '')) AS maxnum
FROM tbl
WHERE 
  name LIKE '<the name to search>%'

Or instead of LIKE, using a regular expression, which is more accurate than LIKE (in case a name contains another name, the LIKE might match) but more expensive:

SELECT
  MAX(REPLACE(name, <the name to search>, '')) AS maxnum
FROM tbl
WHERE 
  name REGEXP '^<the name to search>[0-9]+$'

edited Jul 7, 2012 at 21:03

answered Jul 7, 2012 at 20:45

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

Comments

Alex Wilson · Accepted Answer · 2012-07-07 20:45:30Z

1

I would do this with an additional table with two columns and store in this table each name and the last assigned id. And then replace your nameBase+number column in your original table with a name column being a foreign key to the addition table, and a number column, being the appropriate count for that entry.

This will be much easier and more efficient to manipulate.

answered Jul 7, 2012 at 20:45

Alex Wilson

6,79030 silver badges44 bronze badges

2 Comments

Sara Over a year ago

Thank you but I am not able to change the schema. This is the requirement the customer has asked me. I have to follow whatever they want!

Alex Wilson Over a year ago

@Sara. Ouch. Well Michael below has a reasonable answer for you. But it's not going to be very pretty, because you won't be able to index the column in the way you need for efficient updating.

Chris Trahey · Accepted Answer · 2012-07-07 20:47:39Z

If possible, I would restructure the table to place these in either 2 tables (better) or at least two columns (medium). The structure you have is not normalized at all :-/

Without knowing too much about your schema; here is my recommendation for the two-table solution: (note: this is normalized and also follows the idiom "Do not store that which can be calculated")

names
------
id  | name
01  | test
02  | stack

name_hits
-------
name_id | date
01      | 01/01/2001
01      | 01/15/2001
01      | 04/03/2001
02      | 01/01/2001
...

and then select like this:

SELECT names.name, count(name_hits.id) as hits 
FROM names JOIN name_hits ON names.id=name_hits.name_id
GROUP BY names.id

and insert like this:

INSERT INTO name_hits SELECT id, NOW() FROM names WHERE name = "stack";

Gordon Linoff · Accepted Answer · 2012-07-07 20:57:49Z

Presuming that you are unable to change the structure of the table, you can do what you want. However, it is rather expensive.

What you would like to do is something like:

select name
from t
where left(name, length(<name parameter>)) = <name parameter>
order by name desc
limit 1

Unfortunately, your naming probably does not allow this, because you are not left padding the numeric portion with zeroes.

So, the following gets around this:

select name,
       cast(substring(name, length(<name parameter>), 1000) as int) as number
from t
where left(name, length(<name parameter>)) = <name parameter>
order by 2 desc
limit 1

This is not particularly efficient. Also, indexes cannot really help with this because the collating sequence for strings is different than for numbers (test0, test1, test10, test100, test11, etc. versus 0, 1, 2, 3, 4 . . .).

If you can, I would follow the advice of the others who suggest multiple columns or tables. I only offer this as a method where you don't have to modify the current table.

Chris Trahey · Accepted Answer · 2012-07-07 21:01:38Z

1

If you cannot change the schema, try this:

INSERT INTO names (name)
  SELECT CONCAT("stack", CAST(TRIM(LEADING "stack" FROM name) AS INT)+1) 
   WHERE name LIKE "stack%" ORDER BY name DESC LIMIT 1;

The idea is:

select the "highest" previous value,
chop of the name,
cast the remaining string as an int,
add one to it,
then put the name back on it.

I have not tested this... I hope it leads you in the right direction.

Note that I have used a constant string "stack" as an example, you will likely want to make that dynamic.

answered Jul 7, 2012 at 21:01

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

Collectives™ on Stack Overflow

MySQL find the last occurrence of a string pattern in a specific column

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related