I need some suggestions on the best way to do this. I have a field in my SQL Server 2000 database.
It is defined as NVARCHAR[50]. This field is to store searchable alfa-numeric data. On the user application they can enter the data using wildcard characters (*) i.e. A975*78*901 The wildcard character can represent any alfa or numeric character. Another application will receive the full id i.e. A975378Z901 the applciation will have to find all records in the database where this id is a match. I do not think it is feasible to create every possible combination and store each one in the database

i.e.
A975A78A901
A975B78B901
A975C78C901
etc...

But I do not know how to do it. If I store the entered id with the wildcards in the database I cannot get a queries to successfully find matches to the "real" id. I have tried using the BETWEEN and LIKE % statements in the queries with no luck yet. Can someone tell me the best way to do this? Thanks so much!