v_value FROM ( select row_number() over () as idx, -- generate an index for each element i as v_value from UNNEST(String_To_Array('12;5;25;10;2',';')) i ) as a JOIN ( select row_number() over() as idx, i as v_value from UNNEST(String_To_Array('10;20;;',';'))...
1. postgresql 某字段用逗号分隔,查询某个值是否在其中 比如有个字段值是 1,2,3 查看1是否在里面 select id, gate_type, gate_name from yg_gate_base_b where'1' = ANY ( STRING_TO_ARRAY( gate_type, ',') ) order by gate_type desc 2. postgresql 某字段用逗号分隔,关联表查询 比如一个表,...
'Tag04');insertintobooksVALUES('01','Book01','01');insertintobooksVALUES('02','Book02','01,02');insertintobooksVALUES('03','Book03','01,02,03');-- 查询1-- 找到任一匹配的标签-- STRING_TO_ARRAY 字符串--> Array
在PostgreSQL中,string_to_array函数用于将字符串转换为数组。下面是对你问题的详细回答: string_to_array函数的作用: string_to_array函数的主要作用是将一个由指定分隔符分隔的字符串转换为数组。这在处理包含多个值的字符串时非常有用,尤其是在需要将这些值作为独立元素进行处理时。 string_to_array函数的基本语法...
select array_to_string(array_agg(distinct ref_no), '&') from cnt_item where updated_on between '2021-05-05' and '2021-05-30 16:13:25'; --合并结果:ITM2105-000001&ITM2105-000002&ITM2105-000003 分割字符串 string_to_array函数可以分割字符串,返回值是一个数组: ...
string_to_array函数和ANY一起使用用法 select code from ods.my_table t where id=374; 当我查询出来的结构集为: 1,2,3 //但是我有需要把他们拆分成一个数组进行处理,可以通过 string_to_array函数 select string_to_array(code,’,’) from ods.my_table t where id=374; ...
SELECT TO_CHAR(45.67, '999.99'), TO_NUMBER('123') AS num, TO_DATE('2023-09-15', 'YYYY-MM-DD') AS date; 数组的转换 ARRAY_TO_STRING()函数将数组转换为字符串。 STRING_TO_ARRAY()函数将字符串转换为数组。 示例代码: SELECT ARRAY_TO_STRING('{1, 2, 3}', ',') AS array_to_string...
Example 2: How Does the STRING_TO_ARRAY() Function Work on Table’s Data in Postgres? We have created a table named “employee_data” that consists of three columns: emp_id, emp_name, and emp_email_address: SELECT*FROMemployee_data; ...
( SELECT string_agg(ui."name",',') FROM user_info ui WHERE CAST ( ui.user_id AS VARCHAR ) = ANY ( string_to_array( alarm_user, ',' ) ) ) alarm_user_name, (select string_agg(sdd.dict_value,',') from sys_dict_data sdd where CAST(sdd.dict_code as VARCHAR)= ANY(string_to...