Because work to clean up data, data quality is very poor, there are instances when the customer table temp_cust_info, there are instances when
Fields are as follows:
Customer number family address unit of the mobile phone number of the office telephone
130102556 Beijing 12345 A
130102556 A12345 789013 Beijing Beijing B
There is equal to two data, belong to the data quality is bad, need treatment, and how to get a record

Customer number family address unit of the mobile phone number of the office telephone
789013 130102556 12345 | A12345 Beijing Beijing B


If you want to write a stored procedure, please feel free to comment, or writing a function

CodePudding user response:

Data governance in most cases it is difficult to purely technical means to solve, also do not recommend by technical means to solve, suggested that query the customer number is not the only data list, find the corresponding business personnel to confirm

CodePudding user response:

SELECT the customer number,
WMSYS. WM_CONCAT (DISTINCT cell number) phone number,
WMSYS. WM_CONCAT (DISTINCT office phone) office telephone,
WMSYS. WM_CONCAT (DISTINCT family address) family address,
WMSYS. WM_CONCAT (DISTINCT units address) address
The FROM TEMP_CUST_INFO T
GROUP BY customer number



Delimiters themselves under the processing line over there

CodePudding user response:

WMSYS WM_CONCAT taught, I see there are kinds of methods, in this way, ready to try today, and then compare the efficiency of this method with you,
Select the customer number,
Regexp_replace (listagg (phone number, ', ') within group (order by customer number) and '([^,] +', '\') as a phone number, 1
Regexp_replace (listagg (home address, ', ') within group (order by customer number) and '([^,] +', '\ 1') as home address,
Regexp_replace (listagg (unit address, ', ') within group (order by customer number) and '([^,] +', '\ 1') as unit to address the from TEMP_CUST_INFO
Group customer number;
But feel more complicated than you, don't know which is really good, I have to compare, thank you teach

CodePudding user response:

Wmsys. Wm_concat efficiency than listagg within group to lower this according to the usage scenario, wmsys return is clob, so support characters long, listagg return can only be varchar2 (4000), if the length is less than using listagg, if the super length, use wmsys, personal advice