Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    10

    Unanswered: Some SQL Help Please

    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!

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    how about this:

    where id like [A-z]975[A-z]78[A-z]901

  3. #3
    Join Date
    Jul 2003
    Posts
    10
    Originally posted by ms_sql_dba
    how about this:

    where id like [A-z]975[A-z]78[A-z]901
    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.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    ok, how about if you replace * that comes from your FE with [0-z] right in the where clause, like this:

    where id like (@id, '*', '[0-z]')

  5. #5
    Join Date
    Jul 2003
    Posts
    10
    Well the data stored into the database is the id with the wildcard characters. the search is made against the database using the full id value.

    Something like:
    DECLARE @id NVARCHAR(50)

    SELECT @id = NA975378Z901

    SELECT * FROM [Barcode Schemes] WHERE @id BETWEEN [Barcode Schemes].[Begin Range] AND [Barcode Schemes].[End Range]

    Maybe I could store the data to the database using the [0-z] identifier.

    i.e.
    User enters A975*023*5423

    Data in the Db looks like A975[0-z]023[0-z]5423

    Would this work?

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    i forgot REPLACE in front of (@id, '*', '[0-z]')

  7. #7
    Join Date
    Jul 2003
    Posts
    10
    Thank you for your help. I figured it out and it works nicely so far. Your idea on using the [0-z] search range lead me in the right direction. thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •