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 senseIf 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|||Same as Pat mentioned, Addition is, it will handle records which dont have '(' value.

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+'(%'|||including the "(" in the pattern
where Reference+'(' like '5(%'