Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: Incorrect Values

    Not to sure how to explain this, but will give it my best shot.

    In the table I have values as listed below:

    Reference
    -----------------------
    5(a)
    5(b)
    5(b)(c)
    50(a)(b)(c)
    50(a)(b)(e)
    55
    55(a)(f)(g)

    When a user searches for the 5 it should only bring rows 1,2 and 3. When he search for 50 it should only bring out rows 4 and 5. You cant use the LIKE keyword, as this brings out everything starting with 5.

    Thanks...

    Hope that make sense

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you could switch to canonical form so that the "55" becomes "55()", then you could use LIKE by including the "(" in the pattern. If not, you'll have to either parse the reference value, or write a multi-stage search function that returns the set of matching references (by doing an inclusive search, then discarding the false positive values).

    -PatP

  3. #3
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Same as Pat mentioned, Addition is, it will handle records which dont have '(' value.
    Code:
    set nocount on
    go
     
    create table #ReferenceTable
    (
    Reference varchar(200)
    )
    go
    ------insert query
    insert into #ReferenceTable select '5(a)'
    union
    select '5(b)'
    union
    select '5(b)(c)'
    union
    select '50(a)(b)(c)'
    union
    select '50(a)(b)(e)'
    union
    select '55'
    union
    select '55(a)(f)(g)'
    go
    ---selection query--------
    declare @searchvalue varchar(100)
    set @searchvalue='50'
    select * from #ReferenceTable where Reference= @searchvalue or Reference like @searchvalue+'(%'
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    including the "(" in the pattern
    Code:
    where Reference+'(' like '5(%'

Posting Permissions

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