select, replace and union all

We have some database entries with the word DOC in their name.  We have some database entries without that word, but which have the same other words, like: ABC and ABC Doc.  They appear to be different, in units and counts, but hard to tell.  I was told to make a comparison.

The first step was to get the ‘doc’s, which was a select where name like ‘%DOC’.  EasyPeasy.  I GUESS the second step could have been:  get a list of all names where exists name + ‘  ‘ + doc, but instead, i got all the docs in a temp table, then removed the ‘doc’ from the name and looked up those.  Lots of docs did not have shorter name entries, some did.

select distinct [name] into #nameTemp FROM [table] 
where name  like '%doc'

SELECT distinct [name], units, count(*) FROM [table] 
where name in (select rtrim(ltrim(replace(name, 'doc',''))) 
from #nameTemp) group by name, units
union
SELECT distinct [name], units, count(*) FROM [table] 
where namelike '%doc' group by name, units

So we get output of the name, units and count for the short names and then the doc names.  I had to take them into Excel and short them, but it worked and it was easy.

Only a little harder is using a CTE (common table expression) as the first select.  Make the first select as the inside query with ;WITH CTE as ( … query here …), put the results into a temp table (notice the first select into and the absence on the following) and sort that

drop table #nameTemp
;with CTE as
(
select distinct [name] FROM [table] where name like '%doc'
)
SELECT distinct [name], units, count(*) as count into #nameTemp
 FROM [table] 
where name in 
(select rtrim(ltrim(replace(name, 'doc',''))) from CTE)
 group by result_name, units
union
select [name], units, count(*) as count FROM [table] 
where name like '%doc' group by name, units
select * from #nameTemp order by result_name
Advertisements