I have two tables, #customer and #abbr. The #customer table consists of two columns: 'id' and 'name,' while the #abbr table includes entries for abbreviations and their corresponding full forms. My goal is to replace all abbreviations in the customer names with their respective full forms. For instance, if a customer name is 'Object ME CT,' I expect the result to be 'Object Medical Control.' However, with my current query, it yields two rows: 'Object ME Control' and 'Object Medical CT.' I am aiming for a single row per 'id,' where all abbreviations are replaced with their full forms from the #abbr table. How can I achieve this? Thank you!
create table #customer ( id int identity(1,1) ,cname varchar(100) ) create table #abbr ( abbr varchar(100), fname varchar(100) ) insert into #customer values('Assign ME CT'),('Assign ME'),('Assign CT') insert into #abbr values ('ME','Medical'),('CT','Control'),('MMT','Metro') select *, REPLACE(c.cname, a.abbr,a.fname) from #customer c join #abbr a on c.cname like '%'+a.abbr+'%' where id = 1