Altering string (Text to column function)

Question

I have two tables like the one listed below:

+----+----------------+-----------+-----+
| id | Catalog Number | Pack Size | Qty |
+----+----------------+-----------+-----+
|  1 | K353-J353-5    | 5 kg      |   3 |
|  2 | V3532-3        | 3 kg      |   2 |
|  3 | S32-366-10     | 10 kg     |   1 |
+----+----------------+-----------+-----+

+----+----------------+--------------+
| id | Catalog Number | Product Name |
+----+----------------+--------------+
|  1 | V353-J353      | a            |
|  2 | V3532          | b            |
+----+----------------+--------------+

I would like to join these two tables but the different catalog number format is giving me trouble. The catalog numbers is always split by the "-" char, but could have 0 or 1 "-".

The only way I've managed is through Excel's text to column function, is there an equivalent way in SQL?

Thanks in advance, Mike

this could help: http://stackoverflow.com/questions/1930809/mysql-join-query-using-like — Jan Zeiseweis, Aug 27 '13 at 23:05
Could you state exactly which catalog number to match with which? Do you want to match if one catalog number is the begin of the other, and the shorter one can be in either of both tables? — FrankPl, Aug 27 '13 at 23:16
The formula Thomas posted below helped a lot. It works fine without the REPLACE function. It was basically matching two catalog number where one has an extra substring separated by a dash. By removing the end substring, it worked out perfectly. Thanks! With my table, the catalog number was formatted in a way that Jan's solution also worked. Double Thanks! — Michael Cheng, Aug 28 '13 at 16:04

Thomas Kelley · Answer 1 · 2013-08-27T23:23:54.673

When you JOIN tables, it's valid to use MySQL functions in the ON clause:

SELECT * FROM
Table1
JOIN Table2
ON REPLACE(Table1.catalog_number, "-", "") = REPLACE(Table2.catalog_number, "-", "")

This would join columns by Catalog Numbers of V35323 and V3532-3, but not by Catalog Numbers of V3532 and V3532-3. According to the question itself, this should solve the problem. In looking at the data though, I'm not so sure.

Edit

Your question doesn't state it specifically, but I think you're looking to disregard the last group of digits from the first table, and match it to the unaltered catalog number from the second table. If that's true, then this will work:

SELECT * FROM
Table1
JOIN Table2
ON
REPLACE( SUBSTR(Table1.catalog_number, 1, LENGTH(Table1.catalog_number) - LENGTH(SUBSTRING_INDEX(Table1.catalog_number, "-", -1)) - 1), "-", "")
=
Table2.catalog_number;

It's crazy convoluted, but it does the trick:

mysql>     SELECT * FROM
    ->     Table1
    ->     JOIN Table2
    ->     ON
    ->     REPLACE( SUBSTR(Table1.catalog_number, 1, LENGTH(Table1.catalog_number) - LENGTH(SUBSTRING_INDEX(Table1.catalog_number, "-", -1)) - 1), "-", "")
    ->     =
    ->     Table2.catalog_number;
+----+----------------+-----------+------+----+----------------+--------------+
| id | catalog_number | pack_size | qty  | id | catalog_number | product_name |
+----+----------------+-----------+------+----+----------------+--------------+
|  2 | V3532-3        | 3 kg      |    2 |  2 | V3532          | b            |
+----+----------------+-----------+------+----+----------------+--------------+
1 row in set (0.02 sec)

score 0 · Answer 2 · answered Aug 27 '13 at 23:07

0

Try:

SELECT *
FROM Table1
JOIN Table2 ON Table2.`Catalog Number` LIKE CONCAT(Table1.Catalog Number, '%')

answered Aug 27 '13 at 23:07

Jan Zeiseweis

3,718
2
17
24

Altering string (Text to column function)

2 Answers2