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