字符串函数

select to_number('12,454.8-', '99G999D9S');

create table city_test(
  country text,
  city text
);
insert into city_test values ('中国' ,'上海'),('中国','台湾'),('日本','东京'),('法国','巴黎'),('英国','伦敦');
select string_agg(city,',') from city_test;

string_agg
------------------------
上海,台湾,东京,巴黎,伦敦

create table name_text(student_id int,name text)
insert into name_text values (1,'张三'),(2,'李四'),(3,'王五'),(4,'周六');
select string_agg(name,',') filter (where student_id >2) from name_text ;

string_agg
----------
王五,周六

create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select name,regexp_split_to_table(intrests, ',') from interests_test;

name | regexp_split_to_table
---------------------------
张三 | 唱歌
张三 | 跳舞
李四 | 踢球
李四 | 跑步
李四 | 画画
王五 | 插花
王五 | 书法
王五 | 弹琴
王五 | 睡觉

create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select name,regexp_split_to_array(intrests, ',') from interests_test;

name | regexp_split_to_array
----------------------------
张三 | {唱歌,跳舞}
李四 | {踢球,跑步,画画}
王五 | {插花,书法,弹琴,睡觉}

create table animal_test(
  animal text,
  color text );
insert into animal_test values ('狗','白色'),('猫','白色'),('老虎','黄色');
select animal,replace(color,'白','五彩斑斓') from animal_test;

animal |  replace   
-------------------
狗     | 五彩斑斓色
猫     | 五彩斑斓色
老虎   | 黄色

create table a_test(a text);
insert into a_test values ('Abcd1234abCd');
SELECT regexp_replace(a, '1234.', '77', 'ig') from a_test;

regexp_replace 
--------------
Abcd77bCd

select regexp_match('foobarbequebaz', '(bar)(beque)')

regexp_match
------------
{bar,beque}

create table  interests_test(name text,intrests text);
insert into interests_test values ('张三','唱歌,跳舞'),('李四','踢球,跑步,画画'),('王五','插花,书法,弹琴,睡觉');
select row_to_json(t) from (select name,intrests from interests_test) as t;

row_to_json                
------------------------------
{"f1":"张三","f2":"唱歌,跳舞"}
{"f1":"李四","f2":"踢球,跑步,画画"}
{"f1":"王五","f2":"插花,书法,弹琴,睡觉"}

create table row_test(a text,b text);
insert into row_test values('0','0'),('1','0');
select a,b from row_test where row(a,b)=row(b,a);

a | b 
------
0 | 0

create table split_part_test(a text);
insert into split_part_test values ('a/b/c/d/e'),('a1/b1/c1/d1/e1');
select split_part(a, '/', 2) from split_part_test;

split_part 
----------
b
b1

select concat('abcde', 2, NULL, 22);

concat
---------
abcde222

select concat_ws(',', 'abcde', 2, NULL, 22);

concat_ws
----------
abcde,2,22

select substring('Thomas' from 2 for 3);

substring
----------
hom

select substring('Thomas' from '...$');

substring
----------
mas

select substring('Thomas' from '%#"o_a#"_' for '#');

substring
----------
oma

select left('hologres', 4);

left
----
holo

select right('hologres', 4);

right
----------
gres

select 'Holo' || 'greSQL';

select bit_length('jose');

select char_length('jose');

select length('jose');

select lower('TOM');

select upper('tom');

select initcap('hi THOMAS');

select octet_length('jose');

select position('om' in 'Thomas');

select strpos('high', 'ig');

select trim(both 'xyz' from 'yxTomxx');

select btrim('xyxtrimyyx', 'xyz');

select ltrim('zzzytest', 'xyz');

select rtrim('testxxzx', 'xyz');

select lpad('hi', 5, 'xy');

select rpad('hi', 5, 'xy');

select md5('abc');

select parse_ident('"SomeSchema".someTable');

select quote_ident('Foo bar');

select quote_literal(E'O\'Reilly');

select ascii('x');

select chr(65);

select repeat('Pg', 4);

select starts_with('alphabet', 'alph');

select to_hex(2147483647);

select translate('12345', '143', 'ax');