0

no script to attach as I can pull the data just fine, I am struggling to join my two tables though.

The column I want to join on has the same information formatted differently in each table, making it worse, one of these columns uses wildcards for the program that takes data from there.

Example of what I mean:

T1.Col1 would contain the actual value, say EXAMPLE A , EXAMPLE B , EXAMPLF A

T2.Col1 would have one entry like EXAMPL*[AB]

The Program using that table would treat all values the same if it started in EXAMPL, and ending in EITHER A or B.

Meaning the following would all be a match, this is what I need to recreate for my script:

EXAMPLE A , EXAMPLE B , EXAMPLF A

Can I recreate that logic in SQL? To find a match/closest match based on certain conditions? T2 Column contains about 200 values just like that and all different, so I need a solution that can apply to everything and not just this case.

I've tried formatting T2 values using Replace to be more like T1 but the square brackets are killing me. Making new rows in the output table might help but we're at the limit of what I can do in SQL.

0

1 Answer 1

0

To achieve that result you would have to use the LIKE operator and a REPLACE on the join like this:

CREATE TABLE T1 (Col1 varchar(256));

INSERT INTO T1
VALUES ('EXAMPLE A'), ('EXAMPLE B'), ('EXAMPLF A');

CREATE TABLE T2 (Col1 varchar(256));

INSERT INTO T2
VALUES ('EXAMPL*[AB]');

SELECT T1.Col1, T2.Col1
FROM T1
    INNER JOIN T2 ON T1.Col1 LIKE REPLACE(T2.Col1, '*', '%');

If you check the Arguments section of the logical operator LIKE, you'll see that the * does not stand for 'any string of zero or more characters' in SQL Server that's the role of %. The use of square brackets of your sample on the other hand is the same used in SQL Server, so it will work as expected without any change.

2
  • Thanks @Ronaldo and Sorry for the delay, It worked perfectly and it shows that I have a lot more to learn. Learning on the fly as I need it isnt working out anymore, I should do a class!
    – Oisin
    Mar 20, 2021 at 4:42
  • Glad I could help, @OisinFeeney. About you having a lot more to learn, just keep visiting the forum and I'm sure you're gonna benefit from that. Answering questions like yours (or just reading the answers of more experienced users when I don't have the knowledge to answer the questions myself) has made me learn a lot of new stuff =)
    – Ronaldo
    Mar 20, 2021 at 22:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.