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 :)
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.