Hi All,
I am trying to replace below words or chars from a given name field(company name or person's name) and I want a generic SQL user defined function which should remove all these given words or chars that are passed to the UDF function.
Basically I am storing all these chars or words in a Title table and in future if I add any new word or char to the Titles table then the function should remove the given word or char from the FullName field.
My Source field FullName values looks like below..
FullName:
John Doe Corporate
Mary Jones llp
Doctor Whu ltd
Leia & Organa MD
A&O Office
Dr. Ray Wed
James Adams Jr.
William Duke iii
Mr.John King Sr.
First Step, Inc.
A-Z Needs, Inc.
ABC 100 Sales, ltd
I need a UDF function to show output in below format. I am using SQL server 2016 & 2019 version
Note: 1) I have millions of records to process, so need something which is faster to process(not cursor functionality) the records.
2) It should remove numeric values (reg exp) if any.
FullName NewFullName
John Doe Corporate John Doe
Mary Jones llp Mary Jones
Doctor Whu ltd Doctor Whu
Leia & Organa MD Leia Organa
A&O Office AO Office
Dr.Ray Wed Ray Wed
James Adams Jr. James Adams
William Duke iii William Duke
Mr.John King Sr. John King
First Step, Inc. First Step
A-Z Needs, Inc. AZ Needs
ABC 100 Sales, ltd ABC Sales
CREATE TABLE #titles (title nvarchar(50));
INSERT INTO #titles (title)
VALUES (' & '),('&'),('inc'),( 'incorp'),( 'incorporated'),( 'corporation'),( 'corp'),( 'corporate'),( 'llp'),( 'llc'),( 'ltd'),( 'limited'),( 'the'),( 'dr'),( 'md'),( 'rn'),( 'phd'),( 'jr'),( 'sr'),( 'ii'),( 'iii'),( 'iv'),( '3rd'),( 'aprn'),( 'pa-c'),( 'dds'),( 'dmd'),( 'dpm'),( 'dc'),( 'rnp'),( 'crnp'),( 'arnp'),( 'mpt'),( 'cfnp'),( 'canp'),( 'lnp'),( 'fnp'),( 'mba' ),('.'),(','),('-')
CREATE TABLE #Data (fullname nvarchar(100));
INSERT INTO #Data (fullname) VALUES
('John Doe Corporate'),
('Mary Jones llp'),
('Doctor Whu ltd'),
('Leia & Organa MD'),
('A&O Office'),
('Dr.Ray Wed'),
('James Adams Jr.'),
('William Duke iii'),
('Mr.John King Sr.'),
('First Step, Inc.'),
('A-Z Needs, Inc.'),
('ABC 100 Sales, ltd')
Thanks in advance,
RH