CREATE OR REPLACE FUNCTION public.format_number_in_string_with_width(
v character varying, w integer
)
RETURNS text AS
$BODY$
DECLARE
ret text;
BEGIN
ret := v;
with h as (select v as str)
select regexp_replace(regexp_replace(str,'\d+',repeat('0',w) || '\&','ig'),'\d*(\d{'||w::text||'})','\1','ig') into ret from h;
return ret;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.format_number_in_string_with_width(character varying,integer)
OWNER TO root;
将字符串中所有数字串格式化为固定长度带前导0的数字,例如:调用format_number_in_string_with_width(‘B5M‘,3) 将B5M转换成B005M,
于 2022-06-11 12:16:43 首次发布