curiouswebster
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
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
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 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?
ASKER
I meant to show both 6 and 7 digits to the right of the / but am not able to edit my post
ASKER
I think TRIM is better, since I want to trim the left four characters
ASKER
TRIM will not work
I am making two tables with all possible IDs and then post it here. brb
SELECT ...
FROM ... hf
left outer join dbo.APExtGroupArrival FapExtGa on hf.futureAP_id = STUFF(FapExtGa.aupair_id, 1, 4, '')
FROM ... hf
left outer join dbo.APExtGroupArrival FapExtGa on hf.futureAP_id = STUFF(FapExtGa.aupair_id, 1, 4, '')
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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()
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()
So, therefore there is no need fro Trim()
ASKER
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...
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!12345 6', '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]
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!12345
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.
ASKER
thanks
Open in new window