Using Like with variables? I have an odd situation where I have a column called (TransmittalNumber) with datatype of VarChar (75) in a table named (Transmittal Sheet).

I want to query this table to find the transmittal numbers that are duplicated or the ones that starts with the same numbers but ends with a suffix of –W for (West division). Please check the TS# column below:

TSID    TS #    Facility
1    1234       Arizona
2    1234 -W      National
3    2222       Pacif.NW
4    2222 -W      National
5    4444       California
6    4444 -W      National
7    5555        Arizona
8    5555        Arizona


Would something like the following query works?:

declare @TSNumber varchar(75)


set @TSNumber = (select transmittalnumber from TransmittalSheet
)
-   Subquery above returned more than 1 value even with using the IN!
SELECT     TransmittalID, TransmittalNumber, Facility 
FROM         TransmittalSheet ts
where  ts.TransmittalNumber like (@TSNumber) 

union all 

SELECT     TransmittalID, TransmittalNumber, Facility
FROM         TransmittalSheet ts1
where ts1.transmittalnumber like (@TSNumber + '-w')


Prior to 2014, the numbers entered to this column were only added from local regions. Now all TS numbers are entered from a National side, so the older similar local TSs will have –W in the new national entry after a migrate. 

The reason of my query now is to clean up the table from the local ones that still have a current active entry in the national.  

I've tried a self join with no luck still, any advice?

SELECT ts.TransmittalNumber from TransmittalSheet ts
Full Join TransmittalSheet ts2 
ON ts.TransmittalID = ts2.TransmittalID
-- AND 
WHERE ts.TransmittalNumber = ts2.TransmittalNumber + ' -W'


I’m using the SQL Server 2008R2
Your data follows a strict pattern:
If there is more than one entry like #TS%, then the first entry will be the one with #TS, the second will be the one with #TS - W. Obviously, if you sort descending, the order will be reversed.

Therefore the solution seems obvious: group by #TS, get those lines where row_number >1, when sorted descending.

SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
FROM         TransmittalSheet ts


To get only the multiples, you'd put that into a CTE:

With T1 as (SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
    FROM         TransmittalSheet ts)
select * from T1 where T1.RN&gt;1


You could then simply inner join that CTE to your original table on TransmittalID to remove all duplicates.

If the TransmittalNumber has a variable length, you'd also have to use patindex(), in order to calculate your parameter for the left() function.