-
Yitzhak Khabinsky 20,021 Reputation points
2021-05-09T16:38:25.18+00:00 Hi @SQL9 ,
Please try the following solution.
It is pure T-SQL and will work starting from SQL Server 2017 onwards.
It is up to you to package it as a user defined function.I separated the entire process into two steps:
- words cleansing.
- chars cleansing.
SQL
-- DDL and sample data population, start DECLARE @titles TABLE (ID INT IDENTITY PRIMARY KEY, title NVARCHAR(50)); INSERT INTO @titles (title) VALUES (' & '),('&'),('inc'),( 'incorp'),( 'incorporated'),( 'corporation'),( 'corp'),( 'Dr.'), ('corporate'),( 'llp'),( 'llc'),( 'ltd'),('Inc.'),( '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' ),('.'),(','),('-'); DECLARE @badChars NVARCHAR(100) = '0123456789&.,-'; -- DDL and sample data population, end DECLARE @inputParameter NVARCHAR(100) = N'A-Z Needs, Inc.'; DECLARE @output NVARCHAR(100); -- Step #1, cleanse words SELECT @output = STRING_AGG(cleansedWords, SPACE(1)) FROM (SELECT value FROM STRING_SPLIT(@inputParameter, SPACE(1)) WHERE value NOT IN ( SELECT title FROM @titles)) AS t(cleansedWords); -- test, cleansed words SELECT @inputParameter, @output; -- Step #2, cleanse chars SELECT @output = TRIM((SELECT STRING_AGG(REPLACE(TRANSLATE(value, @badChars, SPACE(LEN(@badChars))),SPACE(1),''), SPACE(1)) FROM STRING_SPLIT(@output, SPACE(1)) )); -- test, cleansed chars SELECT @output AS Result;
Hi @SQL9 ,
As ErlandSommarskog said, tsql alone seems to be difficult to achieve your needs. Nested replacement may be possible, but that efficiency is not necessarily higher than your existing cursor.
Regards
Echo