SQL Server SQL query to consolidate names

ztigrdn8 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(94)

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

sql-server

来源：https://stackoverflow.com/questions/77137964/sql-query-to-consolidate-names

2条答案

按热度按时间

nlejzf6q1#

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

赞(0）回复(0）举报 2023-10-15

pdsfdshx2#

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".

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL query to consolidate names

2条答案

相关问题

热门标签

最新问答