Usage instructions for replace and translate in PG (Chinese sorting with less data)

Time:2022-5-10

1. First create the students table


CREATE TABLE students
(
 id integer NOT NULL,
 name character varying(255),
 sex character varying(255),
 class character varying(255),
 "like" character varying(255),
 school character varying(255),
 phone character varying(255)
)

2. Insert data

INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
Values ('1 ',' big cat ',' female ',' first grade ',' painting ',' third primary school ',' 2345 ');
INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
Values ('2 ',' Xiaoyan ',' male ',' third grade ',' calligraphy ',' fourth primary school ',' 2346 ');
INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
Values ('3 ',' Kuku ',' female ',' second grade ',' painting ',' third primary school ',' 2342 ');
INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
Values ('4 ',' Irene ',' female ',' fourth grade ',' calligraphy, piano ',' fourth primary school ',' 2349 ');

result:


select * from students

As shown below:

3. Use of replace


replace(string text, from text, to text)

Return type: Text

Explanation: replace all substrings from the string with the substring to

Example 1:

Select replace ('a black dog ',' Black ',' black and white ')

Result: a black dog becomes a black and white dog

As shown below:

Example 2:

Update students set name = replace (name, 'big cat', 'little cat's sister')

Result: the data whose name is’ big cat ‘name =’ little cat’s sister ‘

Example 3:

Select * from students where school = 'fourth primary school' order by replace (name, 'Irene', '1')

result:

4. Usage of translate


translate(string text, from text, to text)

Return type: Text

Explanation: convert any character contained in the string that matches the character in from into the corresponding character in to.

Example 1:

Select translate ('she's really nice ',' nice ',' beautiful ')

result:

Example 2:

select * from students where phone like '2%' 
Order by translate (class, 'one, two, three, four', '1234')

result:

Example 3:

select * from students where phone like '2%' 
Order by translate (name, 'Library pet cat AI', '1234')

result

Conclusion:

With translate, you don’t worry about Chinese sorting anymore (when there is less data)

Supplement: position and split in PG_ Part, translate, strpos, length functions

I won’t talk more nonsense. Let’s look at the code directly~


select position('.' in '1.1.2.10');
select split_part('1.1.2.10','.',length('1.1.2.10') - length(translate('1.1.2.10','.',''))+1);
select split_part('1.1.2','.',length('1.1.2') - length(translate('1.1.2','.',''))+1);
select length(translate('1.1.2.10','.','a'))+1 as num
select translate('1.1.2.10','.','')
select strpos('1.1.2.10','.')
select instr('1.1.2.10','.',1,3) 
select length('1.1.2.10') - length(translate('1.1.2.10','.',''))

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer. If there are mistakes or not fully considered, please don’t hesitate to comment.