Results 1 to 6 of 6

Thread: Ignore Prefix

  1. #1
    Join Date
    Jan 2012
    Posts
    8

    Unanswered: Ignore Prefix

    Morning!

    This is more of a "how can this be better" question, as to my knowledge the query below functions as expected.

    Overall, I am trying to make a query (TSQL allowed) that takes a user input value (@Input) and sees if it exists in dbo.Table, ignoring prefix characters.

    e.g. T.Value = FOOBAR
    @Input = FOOBAR (true)
    @Input = AFOOBAR (true)
    @Input = FOORBARA (false)
    @Input = FFOOBAR (true)
    @Input = FOOBARR(false)
    @Input = F (false)
    etc..

    Fairly confident the below works, however, I wanted to get other opinions as I am curious if a better/more efficient solution exists.

    SELECT *
    FROM dbo.Table AS T
    WHERE CHARINDEX(REVERSE(T.Value), @Input, 0) > 0
    AND @Input LIKE REVERSE(T.Value) + '%'

    Thanks,

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What is the CHARINDEX used for in the query above?

  3. #3
    Join Date
    Jan 2012
    Posts
    8
    That is actually a good point.. I since added the LIKE check to fix a case that I found out about, and apparently that rendered the CHARINDEX condition useless as well.

    Just using

    SELECT *
    FROM dbo.Table AS T
    WHERE @Input LIKE REVERSE(T.Value) + '%'

    Seems to still give all the results I expect.. Though regardless is using REVERSE() and LIKE a acceptable route to take or is there something more efficient out there?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do you know what PART of @Input you want to look at?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Colour me confused.
    Why the reverse anyways?
    Code:
    WHERE  t.value LIKE '%' + @input
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm assuming the leading values in the variable


    How do you know there are no trailing values (suffix I guess)

    How do you prevent that?

    Is it always only 1 prefix char?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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