Need help in using LIKE

[newphpcoder]

Hi..

 

 

I have 2 tables with Cloth column.

 

sales_order table

Cloth -column

 

sample data of  Cloth

 

3392

3520

3392 Yellow/White

3392 BLUE

3392 RED

3392 Orange

 

cloth_parameter - table

Cloth -column

 

sample Cloth

3392N

3520

3392Yellow/White

3392 Blue

3392 Red

3392Orange

 

How can I equal the two data eventhough the format of data has difference using like.

 

Thank you

 

 

[Barand]

An excellent example of

a) why you should enforce referential integrity

b) why you should avoid using text data as keys

[Barand]

This was the closest I have so far

sales_order
+-----+-------------------+
| sid | cloth             |
+-----+-------------------+
|   1 | 3392              |
|   2 | 3520              |
|   3 | 3392 Yellow/White |
|   4 | 3392 BLUE         |
|   5 | 3392 RED          |
|   6 | 3392 Orange       |
+-----+-------------------+

cloth_parameter
+-----+------------------+
| cid | cloth            |
+-----+------------------+
|   1 | 3392N            |
|   2 | 3520             |
|   3 | 3392Yellow/White |
|   4 | 3392 Blue        |
|   5 | 3392 Red         |
|   6 | 3392Orange       |
+-----+------------------+


SELECT s.sid, s.cloth as cloth_s, c.cid, c.cloth as cloth_c
FROM sales_order s
INNER JOIN cloth_parameter c
    ON REPLACE(s.cloth, ' ', '') = REPLACE(c.cloth, ' ', '')


Result-->
+-----+-------------------+-----+------------------+
| sid | cloth_s           | cid | cloth_c          |
+-----+-------------------+-----+------------------+
|   2 | 3520              |   2 | 3520             |
|   3 | 3392 Yellow/White |   3 | 3392Yellow/White |
|   4 | 3392 BLUE         |   4 | 3392 Blue        |
|   5 | 3392 RED          |   5 | 3392 Red         |
|   6 | 3392 Orange       |   6 | 3392Orange       |
+-----+-------------------+-----+------------------+