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. 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
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!
First of all let me clarify that the ID with the wildacrds will be what is stored in the database or a version thereof. The wildcard character can be in any position of the ID any number of times. I am trying to figure out what is the best way to store the data in the DB for finding matching records using SQL. I could store every possible combination in the DB but that would make way to many records.