How to use regex in mysql query to remove specific characters?

Question

In my query I am using REPLACE( b.DESCRIPTION,'SP. Z O.O.','') AS DESCRIPTION to remove SP. Z O.O. these characters from columns. And hopefully it's working for me. But in my database SP. Z O.O. this characters are stored in different ways. Like sp. Z.o.o, SP. z.o.o etc. Somewhere it's stored in capital letters and somewhere it's stored in small letters. REPLACE( b.DESCRIPTION,'SP. Z O.O.','') AS DESCRIPTION by this method I am only able to remove capital letters. I want all conditions to remove similar words like this. How to apply regex or case in this situation?

This is my query:

SELECT b.TRANS_DETAILS_ID, b.CREDIT_AMOUNT, b.ENTITY_NAME, REPLACE( b.DESCRIPTION,'SP. Z O.O.','') AS DESCRIPTION, DATE_FORMAT(a.TRANSACTION_DATE_TIME,'%d-%m-%Y') AS TRANS_DATE FROM bank_book_transaction_master a, bank_book_transaction_details b WHERE a.TRANSACTION_DATE_TIME BETWEEN '2017-12-01' AND '2017-12-26' AND DEBIT_CREDIT_FLAG = 1 AND a.ORG_ID = '53' AND a.BANK_ID = '14' AND a.TRANSACTION_ID = b.TRANS_MASTER_ID

is there just one `SP. Z...` in description to remove, or possibly more than one in a given description? — ysth, Dec 26 '17 at 18:36
is what's between the Z and the first O really sometimes a space and sometimes a `.`? or is that just a mistake in your examples? — ysth, Dec 26 '17 at 18:50
What collation are you using? Perhaps `..._ci` will do the case-folding you need. — Rick James, Dec 29 '17 at 20:17

score 0 · Answer 1 · answered Dec 26 '17 at 18:36

0

If you are using MariaDB, you could use REGEXP_REPLACE() like the next line:

REGEXP_REPLACE(col, regexp, replace)

Here you will find examples about the usage.

answered Dec 26 '17 at 18:36

Michael Becerra

401
1
3
15

Beware of the wildcard nature of `.`. – Rick James Dec 29 '17 at 20:17

score 0 · Accepted Answer · answered Dec 26 '17 at 19:01

You can do it with a query like this:

UPDATE strtest
 SET mystring = CONCAT( 
         LEFT (mystring, POSITION('SP. Z O.O.' IN  mystring)-1),
         RIGHT(mystring, LENGTH(mystring)-POSITION('SP. Z O.O.' IN  mystring)-9)
         )
WHERE mystring LIKE '%SP. Z O.O.%';

Sample:

Test Table

MariaDB [bernd]> SELECT * from strtest;
+----+------------------+
| id | mystring         |
+----+------------------+
|  1 | SP. Z O.O.       |
|  2 | ABCSP. Z O.O.    |
|  3 | SP. Z O.O.XYZ    |
|  4 | QWESP. Z O.O.IOP |
|  5 | AAASp. Z o.O.LLL |
+----+------------------+
5 rows in set (0.00 sec)

Remove the String in a SELECT ( The >>><<< are only for test)

MariaDB [bernd]> SELECT CONCAT( '>>>',
    ->            LEFT (mystring, POSITION('SP. Z O.O.' IN  mystring)-1),
    ->          RIGHT(mystring, LENGTH(mystring)-POSITION('SP. Z O.O.' IN  mystring)-9),
    ->          '<<<') AS resultstring
    ->        FROM strtest;
+--------------+
| resultstring |
+--------------+
| >>><<<       |
| >>>ABC<<<    |
| >>>XYZ<<<    |
| >>>QWEIOP<<< |
| >>>AAALLL<<< |
+--------------+
5 rows in set (0.00 sec)

To UPDATE the Table

MariaDB [bernd]> UPDATE strtest
    ->  SET mystring = CONCAT(
    ->            LEFT (mystring, POSITION('SP. Z O.O.' IN  mystring)-1),
    ->          RIGHT(mystring, LENGTH(mystring)-POSITION('SP. Z O.O.' IN  mystring)-9)
    ->          )
    -> WHERE mystring LIKE '%SP. Z O.O.%';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [bernd]> SELECT * from strtest;
+----+----------+
| id | mystring |
+----+----------+
|  1 |          |
|  2 | ABC      |
|  3 | XYZ      |
|  4 | QWEIOP   |
|  5 | AAALLL   |
+----+----------+
5 rows in set (0.00 sec)

MariaDB [bernd]>

How to use regex in mysql query to remove specific characters?

2 Answers2