1

I would like to rewrite a number of user id which is splited by "-" between text and number. For example:

  • KT-12345
  • BA-98765
  • CC-98765
  • ITA-87987

From a huge amount of data which is mixed up with text and number. For example:

  • KT98798
  • CC94788
  • BB87600

So the question is : I would like to make the user id from the 2nd examples into the first one. How to achieve it in MySQL. Please suggest.

Wilf
  • 2,297
  • 5
  • 38
  • 82
  • 1
    http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – revoua Feb 16 '13 at 11:48

3 Answers3

1
SELECT
CASE
WHEN floor(substr(name, 3,1)) > 0
THEN CONCAT_WS('-', SUBSTRING(name, 1, 2), SUBSTRING(name, 3, LENGTH(name)))
ELSE CONCAT_WS('-', SUBSTRING(name, 1, 3), SUBSTRING(name, 4, LENGTH(name)))
END AS new_name
FROM test
cetver
  • 11,279
  • 5
  • 36
  • 56
  • 1
    Hi @cetver, your solution is close but I've got some issues. If the user_id prefixed with 2 characters it works well. But if it begins with 3 chars, It results like MK-D-12345. In this case of 3 chars prefixed. I'd like to make it as "MKD-12345". Please suggest – Wilf Feb 16 '13 at 11:57
  • cetver, your solution is super. Thanks a lot. – Wilf Feb 17 '13 at 16:04
0

No "SELECT" data but UPDATE:

UPDATE `table` SET field = REPLACE(field, '-', '');
Stevens
  • 111
  • 3
-1

here an example how to use it in php

$str = 'abc123';
preg_match('#([a-z]*)(\d*)#', $str, $match);
echo 'alpha: ' . $match[1];
echo 'num: ' . $match[2];?>
echo_Me
  • 37,078
  • 5
  • 58
  • 78