This may seem kind of odd. I'm trying to find duplicates in a file although they aren't technically duplicates.
Example Data --- the data below would result in duplicates being found.
SITEID ADDRPRF STREET STS
101 120 MAIN ST AC
105 120 #MAIN ST SS
110 101 #FIRST ST SS
150 101 FIRST ST CL
Ive done this with query, but I can't seem to figure it out with SQL
What I want to do is find streets that have a prefix of (#) and determine if I have a duplicate address without the prefix of (#). Then listing all SITEID's and addresses that are dups.
I'm thinking this is a case for a CTE but I've not used them much.
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.