enumerate suffixes and counts in varchar column

I have a column of VARCHAR values for which each value may contain one of
more dashes ("-" character). I would like T-SQL that enumerate all the
possible values following the FINAL dash character and counts the number of
occurrances of each and loads these into another table (2 col table :
Suffix[nvarchar] and Count[int])First, the obligitory note of reason. Storing data like this is generally a
bad idea. If you need to use substring on a value then it probably needs to
be > 1 columns. Maybe not in your case, but I figure that the dashes aren't
a random pattern, right?
Second, this will give you the result, I think...
create table test
(
value varchar(20)
)
insert into test
select 'hds-f-dsafsaf-asf'
union all
select 'asdfasdfads'
union all
select 'asdfads-asdfas-as'
union all
select 'isd-asd'
go
select case when charindex('-',value) > 0 then
substring(reverse(value),1,charindex('-',reverse(value))-1)
else ''
end
from test
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:Oxjgp2QLGHA.904@.TK2MSFTNGP10.phx.gbl...
>I have a column of VARCHAR values for which each value may contain one of
>more dashes ("-" character). I would like T-SQL that enumerate all the
>possible values following the FINAL dash character and counts the number of
>occurrances of each and loads these into another table (2 col table :
>Suffix[nvarchar] and Count[int])
>|||Yes, I agree. Very bad data structure. Not my idea.
You're code looks interesting, but I also need a count of each distinct
suffix (where a distinct suffix is defined as one that follows the final
dash ("-") character.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23hLtR%23QLGHA.2416@.TK2MSFTNGP15.phx.gbl...
> First, the obligitory note of reason. Storing data like this is generally
> a bad idea. If you need to use substring on a value then it probably
> needs to be > 1 columns. Maybe not in your case, but I figure that the
> dashes aren't a random pattern, right?
> Second, this will give you the result, I think...
> create table test
> (
> value varchar(20)
> )
> insert into test
> select 'hds-f-dsafsaf-asf'
> union all
> select 'asdfasdfads'
> union all
> select 'asdfads-asdfas-as'
> union all
> select 'isd-asd'
> go
> select case when charindex('-',value) > 0 then
> substring(reverse(value),1,charindex('-',reverse(value))-1)
> else ''
> end
> from test
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:Oxjgp2QLGHA.904@.TK2MSFTNGP10.phx.gbl...
>|||Here is the complete solution. Hope this helps get you going...
create table test
(
value varchar(20)
)
insert into test
select 'hds-f-dsafsaf-asf'
union all
select 'asdfasdfads'
union all
select 'asdfads-asdfas-as'
union all
select 'isd-asd'
union all
select 'isd3-asdfa-asd'
go
select suffix, count(*)
from ( select reverse(case when charindex('-',value) > 0 then
substring(reverse(value),1,charindex('-',reverse(value))-1)
else ''
end) as suffix
from test ) as suffixValues
group by suffix
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uhJPFDRLGHA.2668@.tk2msftngp13.phx.gbl...
> Yes, I agree. Very bad data structure. Not my idea.
> You're code looks interesting, but I also need a count of each distinct
> suffix (where a distinct suffix is defined as one that follows the final
> dash ("-") character.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23hLtR%23QLGHA.2416@.TK2MSFTNGP15.phx.gbl...
>

Enterprise Manager rewriting my SQL...is this OK?

I have a T-SQL statement in a DTS lookup, which Enterprise Manager
rather unhelpfully re-orders for me. I was wondering if anyone agrees
that the way it's done it is correct or not?
My original code is...
select a.person_refno
from tblPersons a join tblPersonStats b on a.person_refno =
b.person_refno
where a.Forename = 'John'
and not (b.Code1 = 'AA' and b.Code2 = 'BB' and b.Code3 = 'CC')
...which is converted by Enterprise Manager to...
select a.person_refno
from tblPersons a join tblPersonStats b on a.person_refno =
b.person_refno
where (a.Forename = 'John') and (not(b.Code1 = 'AA')) or
(a.Forename = 'John') and (not(b.Code2 = 'BB')) or
(a.Forename = 'John') and (not(b.Code3 = 'CC'))
Now, I could live with this, IF it had placed extra brackets around
each of the 'OR'ed' sets of statements, e.g. something like
.. ((field1 = x) and (not(field2=y))) OR ((field1 = z) and
(not(field2=y))) ..
Does anyone know if the way EM's done it is OK, i.e. does each 'OR'
automatically get recognized as a separate 'branch'...I always thought
that mixing ANDs and ORs all over the place without wider brackets or
parentheses was bad practice and was liable to confuse the processing!Just to add, I have run a few tests on this using my syntax and
Enterprise Manager's modified syntax (through Query Analyzer) and it
does seem to produce the same results...however, if anyone could
confirm that this mixing of ANDs and ORs without parentheses, that
Enterprise Manager seems to prefer, is still definitely OK as T-SQL
syntax - that would be great.|||It is logically the same, but I always put parenthesis around my criteria
when using ORs to make it perfectly clear what I am trying to do. It is way
too easy to forget the precedence and end up with the wrong data if you do
not explicitly group everything.
That, and I sometimes get myself and read from left to right,
forgetting that AND precedes OR.
Using carriage returns and indenting also help make it more readable for the
next time the code has to be modified.
<champ.supernova@.gmail.com> wrote in message
news:1146062160.096159.44870@.y43g2000cwc.googlegroups.com...
> Just to add, I have run a few tests on this using my syntax and
> Enterprise Manager's modified syntax (through Query Analyzer) and it
> does seem to produce the same results...however, if anyone could
> confirm that this mixing of ANDs and ORs without parentheses, that
> Enterprise Manager seems to prefer, is still definitely OK as T-SQL
> syntax - that would be great.
>|||Thanks Jim, I normally would also add the extra brackets, but EM
doesn't seem to want any of it. I guess the application knows best in
this case!|||The easiest way to prevent EM from messing up your queries is not to let him
near them. :) S refuge in Query Analyzer like most of us do (at least for
any T-SQL development).
ML
http://milambda.blogspot.com/|||IMO, EM is lousy for writing code. I use it for convenience sometimes, but
I never use the code it generates (rewrites) becuase I find it poorly
formatted for the most part. I usually restructure the code when I am done
playing in EM and run it in query analyzer if I need to create any objects,
that way my formatting gets preserved.
<champ.supernova@.gmail.com> wrote in message
news:1146063626.862473.313330@.j33g2000cwa.googlegroups.com...
> Thanks Jim, I normally would also add the extra brackets, but EM
> doesn't seem to want any of it. I guess the application knows best in
> this case!
>