Using MATCHES or LIKE on non-character columns ?

Hello, I need a query that selects all numeric ids that start with the same number. For example I want to select all rows where emp_no starts with 12, but emp_no is a integer field. emp_no ------- 12 123 120003 123434434 Any help would be appreciated ! Thanks in advance -- ******************************************************** DELICom DPD Deutscher Paket Dienst GmbH & Co. KG Wailandtstrasse 1, D-63741 Aschaffenburg Tel.: +49 (0)6021/492-6064 Fax: +49 (0)6021/492-6503 E-Mail: Bernd.Steinhauer@dpd.de Internet:www.dpd.de ********************************************************

Bernd Steinhauer <bernd.steinhauer@dpd.de> a 'crit dans le message : 9692fn$k86d8$1@ID-59884.news.dfncis.de... > Hello, > > I need a query that selects all numeric ids that start with the same number. > For example I want to select all rows where emp_no starts with 12, but > emp_no is a integer field. > > emp_no > ------- > 12 > 123 > 120003 > 123434434 > > Any help would be appreciated ! > WITH A TEMP TABLE LIKE : CREATE TEMP TABLE tmp_table (emp_no_in_char CHAR(12)); INSERT INTO tmp_table SELECT emp_no FROM your_table; SELECT * FROM tmp_table WHERE emp_no_in_char MATCHES "12*"; Good luck. > Thanks in advance > > -- > ******************************************************** > DELICom > DPD Deutscher Paket Dienst GmbH & Co. KG > Wailandtstrasse 1, D-63741 Aschaffenburg > Tel.: +49 (0)6021/492-6064 > Fax: +49 (0)6021/492-6503 > E-Mail: Bernd.Steinhauer@dpd.de > Internet:www.dpd.de > ******************************************************** > >

Bernd Steinhauer wrote in message <9692fn$k86d8$1@ID-59884.news.dfncis.de>... > >I need a query that selects all numeric ids that start with the same number. >For example I want to select all rows where emp_no starts with 12, but >emp_no is a integer field. > That sounds like a really wierd programming requirement, but then I think waking up in the morning is really wierd. Try this: where (emp_no || " ") matches "12*" Unfortunately, the conversion to string prior to the concatenation operator may put leading blanks which will mess up the matches operator. You could also try writing a simple stored procedure to perform the concatentation and trim the leading blanks. In either case, kiss goodbye to the possibility to use indexes. Performance for this requirement could be appalling. I'd be consider denormalising slightly (jeers from the crowd) by adding a column containing the magic first two digits if this is a common type of query. You may be wise to add a contraint tying the two columns together by a rule to ward off evil data inconsistency.