SQL query to consolidate names

Question

Table data has a field 'Name' with data like this

Names	State
Sometimes	WA
Some times	KS
Som etimes	NY
ABCDE Corp	CA
SSS Corp	WA
SSS Corporation	VI
ABCDE	CA
ABCDE Corporation	NJ

Need to consolidate names dynamically

Names	State
Sometimes	WA
Sometimes	KS
Sometimes	NY
ABCDE Corp	CA
SSS Corp	WA
SSS Corp	VI
ABCDE Corp	CA
ABCDE Corp	NJ

Tried below query but no luck.

SELECT * FROM [Table1]
WHERE LEFT([Name], 5) IN 
(
        SELECT LEFT([Name], 5)
        FROM [Table1]
        GROUP BY LEFT([Name], 5)
        HAVING COUNT(*) > 1
)

Any alternate suggestions to derive common name dynamically in SQL Server? Thanks in advance

Answer 1

insert into #temp values (
'Sometimes',    'WA'),
('Some times',  'KS'),
('Som etimes',  'NY'),
('ABCDE Corp',  'CA'),
('SSS Corp',    'WA'),
('SSS Corporation',     'VI'),
('ABCDE',   'CA'),
('ABCDE Corporation',   'NJ')

--#temp is your table1 and the following two lines is all you need to run to get the desired output, just change the table name. 

DECLARE @Whitespace CHAR(4) = CHAR(0) + CHAR(9) + CHAR(13) + CHAR(10);

select Replace(REPLACE(Names, 'e t','et'),'m e','me') Name from #temp



drop table #temp

Answer 2

SELECT REPLACE(REPLACE(REPLACE(Names, ' Corporation', CHAR(7) + 'Corp'), ' ', ''), CHAR(7), ' ') AS NewNames
FROM [Table1]

This will replace "Corporation" by "Corp", remove all blank except those before "Corporation".