sql search function question

wondering how to make entered search text more maleable by sql

[disgustipated]

I wasnt sure where else to post this, so why not here.

I am currently working on quering a databases table's product name column. The column has all Product names like CM08921, CM1928K, CM4013 etc... The SQL query I have written so far goes like this

SELECT Part_Number, Controller, Spec_Filename, Module_Size_W, Module_Size_H, Viewing_Area_W, Viewing_Area_H, Digit_Height
FROM dbo.LCD_CM
WHERE Part_Number LIKE '%Search%'

It works fine if you search "CM" all are returned. If you search CM1 all of the items that start with CM1 are returned, but how would I tweak the code so that say if someone types in CM1000345454 or CM3423987 (something ridiculously long that is not in the database) it would return a product number closest to what it would match. (The second I type in a search of CM2 its fine but if I do CM2000 it wont show anything even though there is a CM2021)
Thanks for any help you can offer, its greatly appreciated.

[Chico_Loco]

I'm not sure if this is the best way, but detect if results are returned, and if not strip a character from the end of the string and search again. Keep doing this until results are returned, or the string is stripped to only 1 character.

In your example above, the three 0's would eventually be removed till the script was searching for just "CM2".

[disgustipated]

Actualy great idea, thanks a ton. I'm coding this with ASP and using Dreamweaver mx2004 (i know I couldnt be any more new to this) but how would I go about if the search returns an empty dataset, to then take the value that was placed in the search form and strip one character off of it until it did return a non empty dataset. Once again thanks for the help, I wouldnt have even thought of taking this route.