SQL –Ignore and remove undesirable characters using Sql. statement.

I wrote a portal for our customers.
Using this portal they can follow-up their orders status.
Our data base contain our item number and our customer
item number.
Usually when our customer want information about specific
product they use their item code.

One of our customers has not consistently codes for his items.
His items look as follow :
 Y-55121 Y 55122 Y55131 55141Y,55141 Y ,55145-Y.

Actually he add Y to his item code to indicate who is the
supplier of this item.
He added Y to his code because our company name start with Y.

This customer want to query our database by using his item code.
i.e. the number without addition 55141,55121 …
for instance.

How to make this search ?
It is imposible to perfom search for every combination because
we have many combinations and also it is very hard to perfom
search from item to item

For example :
Our search is: From 5513 To 5514 and our database contains the following Items:
 Y55134 ,Y-55136 ,Y 55133 ,55131Y ,55135 Y,55132-Y
All these items should appear in our search.

The best way is to ignore the 'Y' and '-' and perform search by
numbers only.

How to do it?

1:- I will replace the 'Y' by *blank using replace function.
2:- Replace '-' by *blank using replace function.
3:- Remove blanks from the field in section 2.
4:- Perfom serach by Item number.

Actually we can make it using one SQL statement as follow



For SQL400
Select * from MyTable where
trim(replace(replace(CusItno,'Y',' '),'-',' '))>='5133' and
trim(replace(replace(CusItno,'Y',' '),'-',' '))<='5134'


For SQL server
Select * from MyTable where
ltrim(rtrim(replace(replace(CusItno,'Y',' '),'-',' ')))>='5133' and
ltrim(rtrim(replace(replace(CusItno,'Y',' '),'-',' ')))<='5134'

PS: In SQL400 we can use trim function to remove blanks from right and left
However there is no such function in SQL Server that’s why we
replace it with 2 functions ltrim that remove blanks from left and
rtrim that remove blanks from right