sql - MySQL Insert with functionality similar to Window's default file naming -
i have table name column. name not added table add default name when new row inserted, window's functionally when creating new file. i'm trying figure out how query number suppose next in sequence.
for example, if table looks this:
id | name ========== 1 | new name (1) 2 | real name 3 | new name
the next inserted row's name should "new name (2)". if table looks this:
id | name ========== 1 | new name (2) 2 | real name
or this:
id | name ========== 1 | name 2 | real name
the next inserted row's name should "new name". if table looks this:
id | name ========== 1 | new name (2) 2 | real name 3 | new name 4 | new name (3)
the next inserted row's name should "new name (1)". far able create query existing numbers ("new name" = 0)
select substring_index(substr(d.name,instr(d.name,'(') + 1), ')', 1) data d d.widget_name regexp '^new[[:space:]]name[[:space:]]\\([[:digit:]]\\)$' union select 0 data d d.name regexp '^new[[:space:]]name$'
now need way to turn list of numbers single number indicate whats next default name enumeration. i've tried using not exists
from question couldn't figure out how use code above both in from
, where
.
i tried creating row_num functionality using a_horse_with_no_name's answer in question. assuming num_data result of query(trying keep clean) table , it's column name name_num:
select ifnull(row_number, (select ifnull(max(name_num),0) num_data)) (select @rownum:=@rownum + 1 row_number, t.* (num_data) t, (select @rownum := 0) r) gap_table gap_table.row_number <> gap_table.widget_num order row_number limit 1;
but didn't seem right too.
if want next name given name, expect query this:
select (case when count(*) = 0 $name else concat($name, '(', max(substring_index(name, ' (', -1) + 0) + 1, ')') end) num_data name rlike concat($name, ' [(][0-9]+[)]' or name = $name;
here sql fiddle demonstrating it.
this assumes name being tested provided parameter called $name
.
note: above query generates new name. should obvious how next number in sequence.
Comments
Post a Comment