Help with a sql query

[sue]

I have a part number column that holds data such as L45-234 , 54I P7-T. or 432.lm, etc. (a combination of alpha, numeric and punctuation). Does anyone know how I would write the select query to search without the space, - or . characters.

I want to use the query to allow the end user to search the "raw part number" so if they entered L45234 then the record for L45-234 would match, 541P7T would match 541 P7-t and 432lm would match 432.lm --- Also, if the user entered 4523 that the L45-234 would match, etc.

Can anyone help me?

Thanks

Sue

[sue]

FYI: In case anyone else has this issue this is how I solved it.

I'm using the output in ASP pages where the user can search for part numbers in the database. Since the formatting can be tricky we wanted to remove the characters that weren't numbers or letters.

What I did was create a view in SQL like:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Part Number], '.', ''), '-', ''), ' ', ''), '/', ''), ',', '') AS RAW, PartID, [Part Number], Vendor, Price, PriceDate
FROM Inventory

That nested the replace function so that I could strip out all commas, periods,forward slashes,and spaces.

Sue

[bule]

You might consider to, when saving records in the database in the first place, to use two columns instead of one for part name. First column would store real part name, and the second stripped version. This would take the load of the server for searches... Just a thought...