Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

How to join on ID, with prefix?

I need to join two table on the ID, but one of the tables has a larger ID value, which I want to ignore

ID (Table #1)
008/0144071

and for the related record, in Table #2
RSM/008/0144071

So, I need to ignore the first four values in the ID column, for the second table.

So, I need to modify this statement:


left outer join dbo.APExtGroupArrival FapExtGa on hf.futureAP_id = FapExtGa.aupair_id

to use the offset  to use this logic.

hf.futureAP_id = FapExtGa.aupair_id[4]

what is the syntax?

Thanks
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Select T1.*, T2.ID 
From [Table #1] T1 Inner Join [Table #2] T2 ON
T1.ID = Substring(T2.ID,5,11)

Open in new window

Avatar of curiouswebster

ASKER

Great. How do I have this compare the rest of the string and not have a defined length?

I just checked the data and see both of the following lengths:

008/0144071

008/1085948

I want this kind of logic
Substring(T2.ID,5,*)

How do I code that?
I meant to show both 6 and 7 digits to the right of the / but am not able to edit my post
I think TRIM is better, since I want to trim the left four characters
TRIM will not work
I am making two tables with all possible IDs and then post it here. brb
Avatar of Scott Pletcher
SELECT ...
FROM ... hf
left outer join dbo.APExtGroupArrival FapExtGa on hf.futureAP_id = STUFF(FapExtGa.aupair_id, 1, 4, '')
SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Where T1.ID = Substring(T2.ID,5,11)      works with string length 11, also
Where T1.ID = Substring(T2.ID,5,100)   works with string length 11 as well
So, therefore there is no need fro Trim()
correction...   Where T1.ID = Substring(T2.ID,5,100)   works with string length 100 as well
So, therefore there is no need fro Trim()
Thanks Mike, but the number 11 in

Substring(T2.ID,5,11)

throws your plan out for me. I do not know that number. But I could easily use your method if also using the .LEN method does not impact performances. But I do prefer the use of the RIGHT, as in:

SELECT   *
FROM T1 INNER JOIN T2 ON RIGHT(T1.ID, aupair_id.LEN-4) = RIGHT(T2.ID,  aupair_id.LEN-4);

I will give this a try...
I don't see why you'd go to all that trouble.  Why not just remove the chars you don't want, as I posted earlier, that seems the most consistent to me, and you never has issues with length:

 STUFF(FapExtGa.aupair_id, 1, 4, '')

E.g.:

create table [Table #1]( ID varchar(25), [Name] varchar(10))
create table [Table #2]( ID varchar(25), [City] varchar(20))

Insert Into [Table #1](ID, [Name]) Values
('008/0144071', 'A')
,('008/0144072', 'B')
,('008/0144073', 'C')
,('008/123456789!123456', 'D');

Insert Into [Table #2](ID, [City]) Values
('RSM/008/0144071', 'NY')
,('RSM/008/0144072', 'SD')
,('RSM/008/0144073', 'SF')
,('ZZZ/008/123456789!123456', 'LI');


Select T1.*, T2.ID, T2.City  From [Table #1] T1, [Table #2] T2  
Where T1.ID = Substring(T2.ID,5,11)

Select T1.*, T2.ID, T2.City From [Table #1] T1, [Table #2] T2  
Where T1.ID = Stuff(T2.ID,1,4,'')

drop table [Table #1]
drop table [Table #2]
I changed Substring(T2.ID,5,11) to Substring(T2.ID,5,100) in my 'Correctiom..." post after that. Because of data type of varchar(25) I have for column T2.ID, it implicitly removes the trailing blank spaces. Good thing, it works.
thanks