Query value based on the length of another?

Transact-SQL
1

Hi everyone, I have a question:

I have COLUMN A and COLUMN B with the following values:

COLUMN A: RAND_123AB, AGAINRANDCBC232, KSJD2432, ZDH443
COLUMN B: 123AB, BC232, D4SS

And what I'd like to achieve, is to first: Get the length of the values for Column B and use it for further queries.
Second: If I have the length for each record, search for that specific record going backwards in column A and see if there's match with a partition on column A.
I tried to visualize here:

image
image863×161 4.4 KB

2

hi hope this helps

i do not have a clear idea of what you are looking for ?

but

create data script

drop table if exists #Data

create table #Data ( columnA varchar(20) , columnB varchar(20))
insert into #Data select 'RAND_123AB','123AB'
insert into #Data select 'AGAINRANDCBC232','BC232'
insert into #Data select 'KSJD2432','D4SS'

select 
     columnA 
from 
    #Data 
where 
    columnA like '%'+columnB

image

3

Hi,

I am looking for a number which shows that how many of the values of Column B were found as a last part of Column A.

4

Not sure I understand what you are saying

please give a clear explanation with example

5

Let me try to make it understandable:

We're checking if Column B has values that can be found in Column A, but only as the last few digits of it. And that amount of digits is based on the length of Column B's value.

So I have "abcde" for Column B, which is 5 characters long. What I'm looking for, is if we have "abcde" as the ending 5 characters of Column A values, for example if we have this value: kdjf_dabcde, then it's a match, because the last 5 characters are matching.
Or another example: "bckLdjskd2" in Column B, which is 10 characters long. Now we're looking at the last 10 digits of Column A, and checking if we have this value, for example: "13K-Ldh:bckLdjskd2", here's a match, because no matter what Column A's first X values, if the last 10 matches with column B.

6

Maybe even like this:

image
image1156×119 4.92 KB

And the end result should be a number of matches

7

that is what i showed you in my attempt ... looks like you are looking for the same thing

but in a different way = an extra column with the wording like you are showing

8 
select 
     columnA
   , columnB
   , case when columnA  like '%'+columnB then 'match because the record ('+columnB+') length in columnB is '+cast(len(columnB) as varchar) +' can be found as last '+cast(len(columnB) as varchar)+' characters of columnA' 
          else ' NO MATCH because the record ('+columnB+') length in columnB is '+cast(len(columnB) as varchar) +' cannot be found as last '+cast(len(columnB) as varchar)+' characters of columnA' 
     end 
from 
    #Data

image
image1385×410 15.7 KB

9

That additional column is basically just an explanation why it’s considered a matching value, the end result should only be a number, which is the amount of matches, so just a “2”. :slightly_smiling_face:

10 

select 
   sum( case when columnA  like '%'+columnB then 1 else 0 end )
from 
    #Data

image

11 
SELECT SUM(CASE WHEN RIGHT([COLUMN A], LEN([COLUMN B]) = [COLUMN B] 
    THEN 1 ELSE 0 END) AS total_match_count
FROM dbo.your_table_name
12

If you can add what the expected output is from your set of data, it would be super helpful. Right now everyone is just guessing.

(and if you created the table and provided insert statements, it would be better still :))

Thanks!