Selecting a table using LIKE

I have an issue where i have 2 tables.  Each table has information related however there is prefix before each item. Some prefixes are 2, 3 , or even 4 characters.  So in order for me to join the tables i need to remove all the prefixes so that i have item ID to Item ID.  

Is there a easier way of doing this for example selecting using Like.  I have 50,00 items that  i need to upate a field on that has the item ID without prefixes.

thanks,
LVL 1
JiggensAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
If one of the tables (e.g. tableb) has the Item ID only, then you could do this.

SELECT *
FROM tablea a
INNER JOIN tableb b ON b.item_id LIKE '%' + a.item_id;

If both tables has a prefix and the length and content of the prefix is undeterminable or disguishable from the item_id, then probably not easily done without first researching some algorithm of cleaning up existing data and try to maintain a consistent pattern going forward.
0
 
chapmandewCommented:
give us an example....
0
 
Atdhe NuhiuCommented:
Are the ids a fixed length once the prefix is removed
eg
aaa0001
bb0003
cccc0006


select *
from tablea a
join tableb b
on substring(a.idfield,len(ltrim(rtrim(idfield))) - 4,4) = substring(b.idfield,len(ltrim(rtrim(idfield))) - 4,4)
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Kevin CrossChief Technology OfficerCommented:
Try this if the ID is always numeric after prefix:

SELECT *
FROM tablea a
INNER JOIN tableb b
ON SUBSTRING(a.idfield, PATINDEX('%[0-9]%', a.idfield), LEN(a.idfield)) = SUBSTRING(b.idfield, PATINDEX('%[0-9]%', b.idfield), LEN(b.idfield));

This will find the first index of number in the string and start the substring there, so don't need to have a consistent length ID for this to work.
0
 
Kevin CrossChief Technology OfficerCommented:
This also works for that:

SELECT *
FROM tablea a
INNER JOIN tableb b
ON RIGHT(a.idfield, PATINDEX('%[^0-9]%', REVERSE(a.idfield))-1) = RIGHT(b.idfield, PATINDEX('%[^0-9]%', REVERSE(b.idfield))-1)
0
 
JiggensAuthor Commented:
Each length of the item is going to be different and in some cases the prefix is on 2 characters and not 4 so i dont understand what the


on substring(a.idfield,len(ltrim(rtrim(idfield))) - 4,4) = substring(b.idfield,len(ltrim(rtrim(idfield))) - 4,4) is actually doing.

Could you explain it to me ?
0
 
JiggensAuthor Commented:
It varies some are numeric some are alpha and they are of all different lengths
0
 
gatorvipCommented:
So what is your procedure for removing all the prefixes so that you have "item ID to Item ID."? How do you tell which prefix is 2 characters vs 4?
Are all your IDs numeric but all the prefixes alpha?
0
 
JiggensAuthor Commented:
NO the IDS are not all numeric they are both alpha and numeric but all the prefixes are alpha. Does that help any?
0
 
gatorvipCommented:
>>NO the IDS are not all numeric they are both alpha and numeric but all the prefixes are alpha. Does that help any?

It doesn't actually help at all. How do you distinguish between a prefix and the actual field?

Take for example the string ABCDX123. How do you know whether the prefix is ABCD and the field ID is X123, or that the prefix is ABCDX and the ID is 123?
0
 
JiggensAuthor Commented:
Thats why i wanted to know if i could just use the item ID on the table without the prefix  do  i wildcard lookup with the items that do have a prefix.

select item_id, Upc_code

From tabe1 and table2

on table1.item_id like table2.item_id using a wildcard search option?
0
 
gatorvipCommented:
Theoretically, yes, if there are no collisions (like the example above).

Practically, we can't possibly know what your data looks like. You haven't even posted sample data yet.

Sure, you can use "like" but that's not going to prevent you from mixing prefixes with field IDs. If you were to perform this join manually, how would you go about it? Is there anything that distinguishes prefixes from IDs?
0
 
Atdhe NuhiuCommented:
I am assuming the digit, the key bit of the string is the same length aa0001 in this case four digits.
Using this I am joining on the last four chars in the string. Obviously if the assumption is incorrect it does not work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.