CustomerCode sort in the table is disordered, we have to deal with this batch of data,
1. Write a SQL statement to take CustomerCode column after a maximum of five digital structure

2. Generate new way of coding inserted into a NewCode, encoding requirements in the form of C00001,
Where C is fixed, according to the CustomerCode behind five after five ascending, starting from 1

CodePudding user response:

The elder brothers, you that post is not back to you

CodePudding user response:

The substr function

CodePudding user response:

Queries can use this statement:

 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 
The FROM crm_customerinfo; 

CodePudding user response:

Update Newcode field can use this statement:

 UPDATE crm_customerinfo a 
The SET a.n ewcode=
(SELECT newcode 
The 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 
The FROM crm_customerinfo) b 
WHERE b.i d=Anderson, d);