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

enum for a column

Is there a way to do something like an enum for a column?
I have a column that should contain one of a fixed set of values. The set of
values won't change and there is only one column in one table that uses this
set of values.
So I was thinking that there wasn't much point in creating a new table just
for this set of values. I could set the data type of the column to a fixed
size char and create a check constraint that tests for each possible value.
There are 20 values in the set so the constraint will be tedious to write.
But there is a notion of order to the set. i.e. value 1 < value 2 < value
N.
So what I really want is an enum like in C++ or C#.
What's the closest approximate in T-SQL?the closest thing to an enum in TSQL is a check constraint.
if you use IN the definition isn't that much more tedious than an enum
definition in C#--you still have to list all of the possible values. For
example,
create table t1
(
f1 int identity(1,1) not null
constraint pk_t1 primary key clustered,
f2 char(5) not null
constraint ck_f2 check (f2 in ('NORTH', 'SOUTH', 'EAST', 'WEST'))
)
"Jonathan Dodds" wrote:

> Is there a way to do something like an enum for a column?
> I have a column that should contain one of a fixed set of values. The set
of
> values won't change and there is only one column in one table that uses th
is
> set of values.
> So I was thinking that there wasn't much point in creating a new table jus
t
> for this set of values. I could set the data type of the column to a fixed
> size char and create a check constraint that tests for each possible value
.
> There are 20 values in the set so the constraint will be tedious to write.
> But there is a notion of order to the set. i.e. value 1 < value 2 < value
> N.
> So what I really want is an enum like in C++ or C#.
> What's the closest approximate in T-SQL?
>
>

Enter Default Values for all columns in all tables except Primary Keys

How can i enter Default Values of " " to all the columns of type character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank youOn Sun, 3 Apr 2005 18:05:46 -0400, serge wrote:

>How can i enter Default Values of " " to all the columns of type character
>of all the tables (excluding system tables) and Default Values of 0
>of all columns of type numbers. Excluding all primary key columns.
>Thank you

Hi Serge,

CREATE TABLE Example (PKCol int NOT NULL PRIMARY KEY,
NumCol int NOT NULL DEFAULT 0,
CharCol varchar(20) NOT NULL DEFAULT '')
go
INSERT Example (PKCol)
VALUES (1)
SELECT * FROM Example
go
DROP TABLE Example
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)