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 !!!
Technology