CODEWITHSUNDEEP
sqlsql-server-2012sql-like
Nickemouse
Nickemouse

Reputation: 53

Comparing two columns with containing one column and an addition

I have an SQL table with a lot of rows. A column in this row is called Label.

The label is a combination of different numbers; example of this is

11-1234-1-1
or
11-1234-12-20

The first two positions are always a combination of 2 (11), after the first delimiter it is always 4 (1234). The third part of the label can be either 1 or 2 values (I.e it can be 1 or 12 or some other random nmr). The fourth part is random and ranging from 1-99

In this table, I also have the exact same values but in the fourth part it leads with 10 or 100 (so the fourth part receives 4 values). Example of this is: 11-1234-12-1020

11-1234-12-20 and 11-1234-12-1020 are the same. I want to find all these values where part B contains Part A.

The labels are found in the same column.

I have joined the columns with each other:

SELECT A.LABEL, B.LABEL
FROM TABLE A
JOIN TABLE B ON A.LABEL = B.LABEL
WHERE ??

What should my WHERE-clause be? I have tried with LIKE and SUBSTRING but I'm missing getting values.

I.e.

WHERE A.LABEL LIKE SUBSTRING(B.LABEL,1,12) + '10' + '%'

Seeing I'm a beginner at this I'm kind of stuck. Help please :)

Upvotes: 1

Views: 150

Answers (2)

connectedsoftware
connectedsoftware

Reputation: 7087

This should work

SELECT A.LABEL, B.LABEL FROM TABLE A
 JOIN TABLE B ON
    CASE WHEN LEN(RIGHT(A.LABEL, CHARINDEX('-', reverse(A.LABEL))-1)) = 1
THEN
    STUFF(A.LABEL, LEN(A.LABEL) - CHARINDEX('-', reverse(A.LABEL))+1, 1, '-100') 
ELSE
    STUFF(A.LABEL, LEN(A.LABEL) - CHARINDEX('-', reverse(A.LABEL))+1, 1, '-10')
END = B.LABEL

So basically we find the last position of a - character in the string by reversing the string:

CHARINDEX('-', reverse(A.LABEL)

Then we insert either a 10 or a 100 at that point to compare with the other labels.

Upvotes: 2

Tony Rogerson
Tony Rogerson

Reputation: 589

You need to do it on the join - remember you are joining two independent sets (tables) and you want the intersection where your pattern matches.

SELECT A.LABEL, B.LABEL
FROM TABLE A
    INNER JOIN TABLE B ON B.LABEL LIKE A.LABEL + '%'

Cheers, T

Upvotes: 1

Related Questions