Conditional Column Filter

Hi
I have a field (FieldA) which I need to Filter on, based upon a parameter,
however the filter is only a substring of the FieldA.
e.g
FieldA = H2/Q3/10
the filter supplied maybe supplied as Q3, H2,or 10, I how to split up FieldA
in it's constituent parts, however in a WHERE clause I don't know how to
represent this.
i.e
If the parameter = Q3
then I would do :
WHERE SUBSTRING(FieldA,4,2) = 'Q3'
However if the param supplied was H2, then :
WHERE SUBSTRING(FieldA,4,2) = 'H2'
would not work?
I would therefore need to change the Column on how its being filtered on, is
there a way I can do this?
Kind Regards
RickyTry:
...
where '/' + FieldA + '/' like '%/' + @.s + '/%'
go
Do not expect SQL Server using properly an index by [FieldA] in case it
exists. You can google for "search arguments", for more info.
AMB
"ricky" wrote:

> Hi
> I have a field (FieldA) which I need to Filter on, based upon a parameter,
> however the filter is only a substring of the FieldA.
> e.g
> FieldA = H2/Q3/10
> the filter supplied maybe supplied as Q3, H2,or 10, I how to split up Fiel
dA
> in it's constituent parts, however in a WHERE clause I don't know how to
> represent this.
> i.e
> If the parameter = Q3
> then I would do :
> WHERE SUBSTRING(FieldA,4,2) = 'Q3'
> However if the param supplied was H2, then :
> WHERE SUBSTRING(FieldA,4,2) = 'H2'
> would not work?
> I would therefore need to change the Column on how its being filtered on,
is
> there a way I can do this?
> Kind Regards
> Ricky
>
>|||Any special reason for breaking the normal form by storing three values in a
single column? Have you considered properly normalizing the model?
Anyway, how about using wildcards:
WHERE FieldA like '%H2%'
For a more helpful answer, please provide DDL, sample data and expected
results.
ML
http://milambda.blogspot.com/|||Hi guys
thanks for the replies, won't wildcards be slow? Incidentally, if I use a
wildcard, when I search for 1 in H1Q11, won't it get with H1 or Q1?
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:E4D9EB2A-D620-4794-9220-3442B02B17C4@.microsoft.com...
> Any special reason for breaking the normal form by storing three values in
a
> single column? Have you considered properly normalizing the model?
> Anyway, how about using wildcards:
> WHERE FieldA like '%H2%'
> For a more helpful answer, please provide DDL, sample data and expected
> results.
>
> ML
> --
> http://milambda.blogspot.com/|||Using wildcards and functions in query conditions is slow, in most cases a
scan will be used. To improve the performance by using indexes first conside
r
normalizing the data.
ML
http://milambda.blogspot.com/|||ok ML, will do, thanks for the suggestion and tip.
Kind Regards
Ricky
"ML" <ML@.discussions.microsoft.com> wrote in message
news:547F6E76-0A5C-43ED-8649-34EEBD431307@.microsoft.com...
> Using wildcards and functions in query conditions is slow, in most cases a
> scan will be used. To improve the performance by using indexes first
consider
> normalizing the data.
>
> ML
> --
> http://milambda.blogspot.com/|||No, the answer Alesandro gave you handles that
(you specified slashes - where have they gone in this followup question?)
Bye
Jan
"ricky" <ricky@.ricky.com> wrote in message
news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Incidentally, if I use a wildcard, when I search for 1 in H1Q11, won't it
get with H1 or Q1?|||Hi Jan
You're quite right, I forgot to place them in.
Well spotted.
Kind Regards
Ricky
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
news:O52SBcijGHA.3440@.TK2MSFTNGP02.phx.gbl...
> No, the answer Alesandro gave you handles that
> (you specified slashes - where have they gone in this followup question?)
> Bye
> Jan
> "ricky" <ricky@.ricky.com> wrote in message
> news:Olme8JijGHA.1204@.TK2MSFTNGP02.phx.gbl...
it
> get with H1 or Q1?
>
>