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?
>
>

condense this working SQL into an algorithm

What is listed below works but I would like to condense it into a loop. Any
ideas? Thank you. -Greg
-- peform substring at these locations in column 1,5,9,13,17,21,25
CREATE TABLE #one
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #one
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #two
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #two
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #three
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #three
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 9, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #four
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #four
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 13, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #five
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #five
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 17, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #six
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #six
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 21, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #seven
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #seven
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 25, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE total
(AreaCode varchar(50),
TimeZone varchar(50))
insert into total
SELECT * FROM #one
UNION ALL
SELECT * FROM #two
UNION ALL
SELECT * FROM #three
UNION ALL
SELECT * FROM #four
UNION ALL
SELECT * FROM #five
UNION ALL
SELECT * FROM #six
UNION ALL
SELECT * FROM #sevenUntested:
INSERT total
SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
, TimeZone
FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
-Alan|||Awesome Alan ! Works perfectly! Now I have to digest what you did along with
my lunch. ;-) -appreciatively -greg
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141750087.253473.75260@.z34g2000cwc.googlegroups.com...
> Untested:
> INSERT total
> SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
> , TimeZone
> FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
> UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
> -Alan
>|||well, first lets break down your results into a set of UNIONed queries:
INSERT total
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
UNION
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
Simple enough, right? Next, when you have multiple entities in your
FROM clause with no JOIN expression, SQL extrapolates all combinations.
SELECT *
FROM (SELECT 1 Number UNION SELECT 2) a
, (SELECT 'A' UNION SELECT 'B') b (Letter)
This is what I did, only I used a subquery that unioned the numbers 0
through 6. I recognized a linear pattern of your start position, so I
used that in the SUBSTRING function. While I could've not used that
formula and used the values 1, 5, 9, et cetera in my UNIONed list of
numbers, I thought it made things a little cleaner to use the formula
in the SUBSTRING function. The above query shows two ways of naming
your columns. I used the latter.
-Alan|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
> instead of CHAR(3)' Likewise area_)code? etc.
char(3) for a time zone? Need char(5), sign + four digits.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> har(3) for a time zone? Need char(5), sign + four digits. <<
That is a time displacement; a zone is "PST', etc. Also, I seem to
remember that we got rid of the "old fractional hours" displacements a
few yers ago, so you can use an integer off of UTC.|||--CELKO-- wrote:
> That is a time displacement; a zone is "PST', etc. Also, I seem to
> remember that we got rid of the "old fractional hours" displacements a
> few yers ago, so you can use an integer off of UTC.
Except there are still places in the world which are, for instance, 5
1/2 hours ahead of UTC (India)
Damien