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

Entering null into blank fields in the database

How can I enter NULL manually in one of the rows in Sql Server database. Those rows does not contain any data.
you can use DbNull.Value. If its a datetime datatype you need to use SqlDateTime.Null and import Sqltypes namespace.
|||well the problem is bit different. I have 4 rows with 4 fields each. Like Name, Age, Address, Phone number.
Now Name, Age , Address is filled with some data. But I forgot to adddata for the phone and now its just blank. How can I make this nullinstead of blank.

|||UPDATE
<table>
SET
[phone]= NULL
WHERe
[phone] = ' '