As the most powerful open source database ,Postgresql( hereinafter referred to as pg) Character processing is also the most powerful , First of all, he also has substr,trim And other databases have common functions , Here we introduce a more powerful function substring, Can be like python,java Use regular expressions like programming languages , Powerful to the extreme

Before that, let's take a look at the four most basic aspects of regular expressions

     % Represents one or more characters     _ Represents a character     ^ Before character    $ After character

pg Its usage on the official website of is as follows , But it's not clear enough , Let me explain one by one

* The first usage and substr almost , Is the specified sequence ,from 2 for 3 Is from sequence for 2 Start taking the position of 3 Characters . The example is as above
* The second example means :$ After character , A dot represents a character , That is, select from the end 3 Characters , Again, if it is substring('Thomas' from
'^....')  The result is Thom
* The third usage is the most practical , It's also the hardest to understand , Let's have a general understanding first :
* from '%#"o_a#"_'  for  '#'  in from It's the beginning ( contain ),for Followed by escape , That's the end
* What this example means is that I want to o_a Combined characters of , among o_a Multiple of (%) Don't use characters , The one in the back (_) Don't use characters , Here we should pay attention to the number of characters left after the segmentation _
* The architecture here can be understood as fixed , Namely   from '#"#"'  for '#'     #"  Is the split character , Can be in #" Select the character you want most
Let's explain the third usage with examples

demand : The query result in the figure below is the query log table , I want 【】 Data in the library , because 【】 The length of Chinese characters is not fixed , I can only use it sql To cut , Therefore, only the third method can be used to obtain the data 【】 Data in the library

solve 1: According to the above thought , I can write this architecture '【#"%#"_______' FOR '#', Try to run it
SELECT SUBSTRING ( log_txt FROM '【#"%#"_______' FOR '#' ) log_txt FROM
operation_log WHERE log_type = '15' LIMIT 3

solve 2: It can also be based on position This function is used to solve the problem , This function is similar to python Of index, It is to convert a character of a string to the number of positions of the character , In this way, it can be used substring The first example of usage

Namely SUBSTRING ( log_txt  FROM 2  FOR  position('【'))

ooook  It's done !!!