?
Solved

Selecting a table using LIKE

Posted on 2009-04-28
13
Medium Priority
?
166 Views
Last Modified: 2012-05-06
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,
0
Comment
Question by:Jiggens
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24251886
give us an example....
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 24251956
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24252110
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24252124
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
 
LVL 1

Author Comment

by:Jiggens
ID: 24252167
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
 
LVL 1

Author Comment

by:Jiggens
ID: 24252229
It varies some are numeric some are alpha and they are of all different lengths
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 24252284
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
 
LVL 1

Author Comment

by:Jiggens
ID: 24252321
NO the IDS are not all numeric they are both alpha and numeric but all the prefixes are alpha. Does that help any?
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 24252398
>>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
 
LVL 1

Author Comment

by:Jiggens
ID: 24253274
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
 
LVL 20

Expert Comment

by:gatorvip
ID: 24253343
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 24253478
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
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 24253785
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question