Like, I could use some SQL help pls

Deebaser

I am trying to join two tables using something like a LIKE condition.

Basically in my dataset I have CUSORS, which are a varchar component of ISORS.
ISORS are in a different feed and are basically in a format that are have a two or three letter prefix to CUSORS.
Example:
Cusor: 1234567AA89
ISORS: XX1234567AA89

I'm drawing a blank here, but basically I want to pull all the ISORS that match the CUSORS in my dataset.


This is what I have so far. It doesn't work, because Im dumb. Can you even do a join on a like?

UPDATE #inst_wo_isor
SET ISor_cusor_db = i.ISOR
FROM
isin_us i
WHERE
#inst_wo_isor.CUSOR LIKE ('%' + i.ISOR)
AND #inst_wo_isor.CUSOR is not null


pls halp or advice

schuss

If you can avoid it, don't use a like, as it's highly inefficient. Is it variable between the 2 and the 3 letter prefixes, or always 1 or the other?

I would do something like
SET Is_Cusor_Db = i_ISOR
From
isin_us_i
WHERE
RIGHT ( ISORS ,11 ) = #inst_wo_isor.CUSOR

My syntax is crap, but the general idea is to do an exact match of the CUSOR to the right portion of the ISOR, eliminating the like.

Lorek

I think your LIKE statement is backwards:
i.ISORS LIKE '%' + #inst_wo_isor.CUSOR

Or for one example:
XX1234567AA89 is like 'any characters here' + 1234567AA89

TehSloth

Is the length of Cusor always the same? I would use SUBSTRING

Where
(#inst_wo_isor.CUSOR = SUBSTRING(i.isor, 2,11) OR #inst_wo_isor.CUSOR = SUBSTRING(i.isor, 3,11)) AND #inst_wo_isor.CUSOR is not null

edit: oh, hey, I like RIGHT better, that's interesting.

schuss

TehSloth wrote: »

Is the length of Cusor always the same? I would use SUBSTRING

Where
(#inst_wo_isor.CUSOR = SUBSTRING(i.isor, 2,11) OR #inst_wo_isor.CUSOR = SUBSTRING(i.isor, 3,11)) AND #inst_wo_isor.CUSOR is not null

edit: oh, hey, I like RIGHT better, that's interesting.

Ha, I did the same thing. My pre-edit post was substring, then the little sql fairy in my head said "Use right you git".

Glad to see other data people think the same way.

Deebaser

RRRRIIIIGGGHHHHTTT
derp. i am dumb
Thank you so much, you beautiful bastards!