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

It would be better to give an example. But CASE will typically use the first condition that evaluates to true, so it will get complicated. — VMai
– VMai, Commented Jun 24, 2014 at 14:40
What are you trying to achieve? Make some samples in source set and desired result row set. Try to explain with at least "pseudo"-SQL. For now it's unclear — Alma Do
– Alma Do, Commented Jun 24, 2014 at 14:41

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 24, 2014 at 16:03

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

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

2 Comments

Fred Wu Over a year ago

Thank you for giving such detailed solutions.

Fred Wu Over a year ago

Actually, my goal is trying to change the architecture of the DB, I need to separate the column into two, to fulfill the 1NF of database. According to your post, I think using application and regex may be the easiest solution, and I'll try in this way. Much appreciated.

Collectives™ on Stack Overflow

Mysql multiple maching patterns in substring_index

1 Answer 1

Architecture solution

Using MySQL

Using external REGEXP solution

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Architecture solution

Using MySQL

Using external REGEXP solution

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related