Mysql multiple maching patterns in substring_index

Question

Can I use something like case to give multiple matching patterns in substring_index?

More specifically in my case, can I matching a set of chars according to their ascii?

Add some examples:

中文Q100
中文T800
中文中文K999

The strings start with some Chinese characters, then following by some numbers or latin letters, what I want is to split the string into two parts: one contains the Chinese characters(from leftmost to the first western letter), the other is from the first western letter to the rightmost.

Like these:

中文, Q100
中文, T800
中文中文, K999

Answer 1

There are multiple ways to resolve a matter. I'll give you 3 of them, starting from most right.

Architecture solution

Using application

Your question is about - replacing by regular expression. And that has a weak support in MySQL (to say precisely, there's no support for replacing by regex). Thus, you may do: select whole record, then split it in applicaition, using a-zA-Z0-9 mask, for example.

Or may be change table structure?

Well, alternative is: may be you should just separate this data to 2 columns? If your intention is to work with separate parts of data, then may be it's a sign to change your DB architecture?

Using MySQL

Second way is to use MySQL. To do it - yes, you'll use REPLACE() as it is. For instance, to get rid of all alphanumeric symbols, you'll do:

SELECT [...REPLACE(REPLACE(str, 'z', ''), 'y', '')...]

that is a pseudo-SQL, since posting whole 26+26+10 instances of REPLACE would be mad (however, using this is also mad). But that will resolve your issue, of course.

Using external REGEXP solution

This is third way and it has two subcases. You may either use UDF or stored routines.

Using UDF

There are third-party libraries which provide regular expression replacement functionality. Then all you need to do is to include those libraries into your server build. Example: lib_mysqludf_preg This, however, will require additional actions to use those libraries.

Using stored routines

Well, you can use stored routines to create your own replacement function. Actually, I have already written such library, it's called mysql-regexp and it provides REGEXP_REPLACE() function, which allows you to do replacements in strings by regular expressions. It's not well-tested, so if you'll decide to use it - do that on your own risk. Sample would be:

mysql> SELECT REGEXP_REPLACE('foo bar34 b103az 98feo', '[^a-z]', '');
+--------------------------------------------------------+
| REGEXP_REPLACE('foo bar34 b103az 98feo', '[^a-z]', '') |
+--------------------------------------------------------+
| foobarbazfeo                                           |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Since it's completely written with stored code, you won't need to re-build your server or whatever.