0

I have a MySQL table with a field of sequential numbers (MS-1, MS-2, ..., MS-100, ..., MS-800, etc.) These numbers are a unique identifier (not primary key) for a set of data. The problem is sorting. Is there a way to take this field and generate a new field to be used for sorting? It would need to take MS-1 and make it ms0001, MS-845 to ms0845, etc.

I need to end up with two fields: one that contains the original value (MS-1) and one that contains the normalized value "ms0001". The MS-1 will be used for display, while ms0001 will be used in a link.

Also, this field would need to be automatically generated for new rows.

Thanks!

Update: As suggested by eggyal, I tried this:

UPDATE Resources 
SET collNum = CONCAT('MS-',LPAD(SUBSTRING_INDEX(eadFaUniqueIdentifier,'-',-1),4,'0')

Resources is the table in question,
collNum is the new field I'm trying to generate,
eadFaUniqueIdentifier contains the original value.

However, I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2. (I'm using MySQL 5.1.61.)

Nathan
  • 766
  • 2
  • 9
  • 19
  • The error is because you're missing a trailing `)` -- my bad, as I omitted it from [my comment](http://stackoverflow.com/questions/11809778/mysql-field-ms-1-to-ms0001/11809992#comment15695279_11809992), from which [@JohnTotetWoo edited his question](http://stackoverflow.com/revisions/11809992/2). Personally, I'd keep the prefix and integer parts in separate columns (of types e.g. `CHAR(2)` and `SMALLINT(4) UNSIGNED`) and then `CONCAT` and/or `LPAD` as required when fetching results. – eggyal Aug 07 '12 at 20:23
  • I agree with your seperation of prefix and integer, unfortunately, I have no control over it. Just trying to work with what I can. Thanks! – Nathan Aug 08 '12 at 13:13

2 Answers2

1
ORDER BY CHAR_LENGTH(field), field
eggyal
  • 122,705
  • 18
  • 212
  • 237
1

take eggyal's answer: https://stackoverflow.com/a/11809954/491243
but if you want to permanently change its values the you can try this:

UPDATE myTable
SET columnA = CASE char_length(replace(columnA,'MS-', ''))
                   WHEN 0 THEN 'MS-0000'
                   WHEN 1 THEN CONCAT('MS-000', replace(columnA, 'MS-', ''))
                   WHEN 2 THEN CONCAT('MS-00', replace(columnA, 'MS-', ''))
                   WHEN 3 THEN CONCAT('MS-0', replace(columnA, 'MS-', ''))
                   ELSE        columnA
              END

myTable - change it to your original's table name
columnA - change to the name of your column which contains MS-* value

As suggested by eggyal:

UPDATE myTable 
SET columnA = CONCAT('MS-',LPAD(SUBSTRING_INDEX(columnA,'-',-1),4,'0'))
Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Or else separate the prefix and integer parts into separate fields and concatenate as required (using [`LPAD()`](http://dev.mysql.com/doc/en/string-functions.html#function_lpad) on the integer?) when retrieving records. – eggyal Aug 04 '12 at 16:01
  • Even if updating the column in-place, you could simplify to `UPDATE myTable SET columnA = CONCAT('MS-',LPAD(SUBSTRING_INDEX(columnA,'-',-1),4,'0')`. – eggyal Aug 04 '12 at 16:06
  • great! i never knew `SUBSTRING_INDEX` existed until now. Thanks! – John Woo Aug 04 '12 at 16:08
  • One more thing (sorry!): if taking this approach, the best way to "automatically generate" such values for new records (as requested) would probably be to use a [trigger](http://dev.mysql.com/doc/en/triggers.html). – eggyal Aug 04 '12 at 16:14
  • John, I'd like to preserve the original (MS-1, etc.). But, I think I can just add a new field and use SET to generate it based on columnA, using your suggestion. Thanks both to John and Eggyal for the help! – Nathan Aug 04 '12 at 20:21
  • @Nathan: Curious why you don't just sort your results as given in [my answer](http://stackoverflow.com/a/11809954/623041)? Does that not achieve what you want? If not, perhaps you could edit your question to explain the additional criteria, as it's otherwise unclear. – eggyal Aug 05 '12 at 00:56
  • @eggyal: I just updated the question with why I need two fields. Sorry for the confusion. – Nathan Aug 07 '12 at 19:41