All Forums Database
txwylde 5 posts Joined 06/10
13 Aug 2014
summing serial numbers in a single column with out using an external function.

I have an audit table that I am creating a view for so I can create some reporting for. In the original table, ther serials are separated by a comma and are all in one comlumn. Here is an example:
 
serial_number                                                          
------------------------------------------------------ -----------------
CO2LM3VGFD57,C8PL83ZZDT9V,D66J23Q0DNQ,DMPGK13SDFJ1                     
YM9402FD72Q,UM830WZZYXX,1B84163E201                                    
F17JLJMSF8GK                                                           
DLXKRBTDF182,DLXKRCD0F182,DLXKRCBZF182,CNU3159MQ4,CNU3 159MN0 CNU314BHYY
C02MC0B1FD58                                                           
DLXMZ0U0FH13                                                           
F18L96KNFH1C                                                           
C02KT0MXDRVG                                                           
V5046NVVETV                                                            
C39JQ6GCDTTR 
 
Here is the query I am using to create the view:
 

select 

cast(date_processed as date),

initiator,

SUM((char_length(serial_number) - char_length(apputil_user.oreplace(serial_number, ','))) + 1) as num_serial

from datamining_biz_app4.legal_data_audit

where serial_number <> ' '

group by 1,2

order by 1,2;

 

 

Is there a way to sum the serials without using the apputil_user.oreplace function?

 

Thanks!

Bill

 

You must sign in to leave a comment.