CODEWITHSUNDEEP
phpmysqlsql
James
James

Reputation: 49

Comparing Data in mySQL

I have searched but cannot find an answer that suits my needs specifically. I have two sets of data and need to compare a piece of the first (table 1 Description field) with a list (table two) and return the VIP codes for each interface/order.

The only identifier that is the same for any of the descriptions is the 9 digit order ID that ends in '003'. I need to compare this 9 digit string to the other table that will always start with the order ID but may contain other characters or numbers afterwards. I know a LIKE comparison will work for the second table but I cannot figure out how to strip the order numbers out of the description field.

UPDATE: Table 1 is a temporary table comparing the output of a router interface command. Table 2 is my static account database that has tens of thousands of entries that I do not want to compare to table one. This is why I do not just take table two and compare the order numbers to table one. I am specifically asking for help with a way to extract the 9 digit order ID from the description field of Table 1.

Table 1

Interface    Description
Ge 1/0/1     blah_bla_123456003_blahlahlah
Ge 1/0/2     blah_blah_bla_234567003_blahahblh
Ge 1/0/3     b_bla_345678003_blhahblah
Ge 1/0/4     bh_blh_ba_456789003_lahlahbl

Table 2

Order ID       VIP Code
123456003.0    Premier
234567003      Wholesale
345678003.6    Normal
456789003.23   Premier

Expected Results

Order*        VIP Code
123456003     Premier
234567003     Wholesale
345678003     Normal
456789003     Premier

*(stripped from Description)

Upvotes: 0

Views: 114

Answers (3)

Eugen Halca
Eugen Halca

Reputation: 1785

you can you something like this :

select TRUNCATE(tbl2.orderId,0) orderNum, tbl2.vipcode, tbl1.interface
from table2 tbl2 , table1 tbl1
WHERE tbl1.description like CONCAT('%',TRUNCATE(tbl2.orderId,0),'%');

i made a fiddle here

Upvotes: 0

Daniel
Daniel

Reputation: 446

If you want to take the first 9 digits of ID from Table 2, you could use left(table2.id,9). It will return the 9 first (left) characters from that field.

Then you can use that with a LIKE (using the "%" wildcard) or using regular expressions.

Upvotes: 1

Sébastien
Sébastien

Reputation: 12139

Why not store the data you need in a dedicated column in each table? Make it a number column and index it, then you can do a very efficient JOIN using that column.

SELECT * FROM Table_1 LEFT JOIN Table_2 USING(common_index)

Upvotes: 0

Related Questions