CAST( string AS UNSIGNED ) question about reliability

[Wesley Furgiuele]

Does anybody have any feedback, particularly problems, using CAST to 
strip the trailing alpha characters from a string in order to convert 
the string to an integer? Is there a better way to do it with MySQL? 
(MySQL 4.0.20, Mac OS X 10.3)

Here's my situation:
TABLE_1
id CHAR( 8 )
TABLE_2
id INT( 8 )
suffix CHAR( 2 )
Sample data would be:
        TABLE_1
        id
123456NN
2345678M
135792
12345678
        TABLE_2
        id              suffix
123456          NN
2345678         MM
135792
12345678
The suffix is not part of the key, but it is included automatically in 
the ID field in TABLE_1. In order to join TABLE_1 to TABLE_2 by id, I 
need to remove any part of the suffix that shows up (since, because of 
the field size limitations, sometimes the whole suffix shows up, 
sometimes part, sometimes none). So what I am doing is:
"SELECT t1.field, t2.field FROM TABLE_1 t1, TABLE_2 t2 WHERE CAST( 
t1.id AS UNSIGNED ) = t2.id".

If anyone knows of a more reliable or elegant method for joining the 
tables based on these fields, any comments would be appreciated.

Wes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]