This can be very helpful when comparing two different lists of companies, matching them when no unique ID is available, and when the company names may be slightly different on both lists.
/*
--
Execution
select
dbo.getCompanyNameCleanForMatching( 'ABC Co. Ltd. L.L.C.');
*/
ALTER function [dbo].[getCompanyNameCleanForMatching]
(
@strCompanyName varchar(max)
)
returns varchar(max)
as
begin
return(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
lower(@strCompanyName)
, '.'
, ''
)
, ','
, ''
)
, '*'
, ''
)
, '('
, ''
)
,
')'
,
''
)
,
' llc'
,
''
)
, ' incorporated'
, ''
)
, ' inc'
, ''
)
, ' limited partnership'
, ''
)
, ' lp'
, ''
)
, ' limited'
, ''
)
, ' ltd'
, ''
)
, 'corporation'
, ''
)
, ' corporate'
, ''
)
, ' corp'
, ''
)
,
'ultimate parent'
,
''
)
,
' co '
,
''
)
,
' par '
, ''
)
, 'group'
, ''
)
, 'company'
, ''
)
, 'international'
, ''
)
, 'holdings'
, ''
)
, 'communications'
, ''
)
, 'industries'
, ''
)
, 'broadcasting'
, ''
)
, 'financial'
, ''
)
, 'products'
, ''
)
, 'technologies'
, ''
)
, 'services'
, ''
)
, 'acquisition'
, ''
)
, 'systems'
, ''
)
, 'manufacturing'
, ''
)
, 'enterprises'
, ''
)
, 'entertainment'
, ''
)
, 'manufacturing'
, ''
)
, 'processing'
, ''
)
, '-dip'
, ''
)
, 'bank loan'
, ''
)
, 'cfs'
, ''
)
, 'hfs'
, ''
)
, 'cif'
, ''
)
, 'global sponsor finance'
, ''
)
, ' - '
, ''
)
, '- '
, ''
)
, ' -'
, ''
)
, '-'
, ''
)
);
end
No comments:
Post a Comment