pg中replace和translate的用法说明(数据少的中文排序)

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)
)

INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
VALUES ("1", "大猫", "女", "一年级", "绘画", "第三小学", "2345");
INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
VALUES ("2", "小厌", "男", "三年级", "书法", "第四小学", "2346");
INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
VALUES ("3", "库库", "女", "二年级", "绘画", "第三小学", "2342");
INSERT INTO "public"."students" ("id", "name", "sex", "class", "like", "school", "phone") 
VALUES ("4", "艾琳", "女", "四年级", "书法，钢琴", "第四小学", "2349");

select * from students

replace(string text, from text, to text)

select replace("一条黑色的狗","黑","黑白相间")

update students set name=replace(name,"大猫","小猫咪的姐姐")

select * from students where school="第四小学" ORDER BY replace(name,"艾琳","1")

translate(string text, from text, to text)

select translate("她真是好看", "好看","漂亮")

select * from students where phone like "2%" 
ORDER BY translate(class, "一二三四","1234")

select * from students where phone like "2%" 
ORDER BY translate(name, "库小厌猫咪艾","1234")

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",".",""))