Search for individual items in a column with DATABASE regular expression

Published in Server
January 22, 2023
1 min read

When adding a column to a table in DATABASE For tables with large amounts of data (tens of millions or more), It takes a lot of time, and if it is serviced in real time, LOCK may occur and service failure may occur.

There are several ways to solve this.

  1. There is a way to save the column format in JSON format, which should be checked to see if there is a supported DATABASE.(MYSQL is supported from version 8 or higher.)
  2. Create multi-purpose columns in advance and save them in order with separators (,) whenever column items are added.
  • If you save it as the content of number 2, you need to search using regular expressions.

Test requirements

DATABASE: MYSQL5.7 version Data Separator: ”|” Data sample:apple|test54|blog

Looking at the regular expression below


Put all characters except \|{0,} separators before and after the FINDTEXT phrase to make LIKE search possible. If you want to search for EQUL, remove \|{0,} at the front and back to become an EQUL condition.

If you want to search for the letter of the second item, you can create a regular expression as shown below.


If you solve it, the first part of the sentence comes with all characters other than the delimiter ”|”, and after the first delimiter appears \|{0,} delimiters were put before and after the FINDTEXT phrase to enable LIKE search. In this way, if the third item comes, you can add a regular expression in this format.




Previous Article
How to change the date type to the form of the desired character



Related Posts

Calculating Date, Time in SQL-mysql
May 16, 2023
1 min