3,492
社区成员
发帖
与我相关
我的任务
分享
SELECT id, customercode, to_number(regexp_substr(customercode, '(\d)+$', 1, 1)),
'C' ||
to_char(row_number() over(ORDER BY to_number(regexp_substr(customercode, '(\d)+$', 1, 1))),
'fm09999') newcode
FROM crm_customerinfo;
UPDATE crm_customerinfo a
SET a.newcode =
(SELECT newcode
FROM (SELECT id, customercode, to_number(regexp_substr(customercode, '(\d)+$', 1, 1)),
'C' ||
to_char(row_number()
over(ORDER BY to_number(regexp_substr(customercode, '(\d)+$', 1, 1))),
'fm09999') newcode
FROM crm_customerinfo) b
WHERE b.id = a.id);