Hello
I thought I was being clever when I arranged a field that could hold any note in it, to be specific and hold the text part of the entry in [ ] and the date enclosed in || (two pipes) example: [RoutingCircuit1, |12/12/2003|]--
Now we want to pull out only what is between the two pipes and use it as criteria. Have worked a little with regular expressions and that is why I thought it would be easier than it seems

Isnt there a whay to say -- Look in THISFIELD and return only what is between the || AS currentdate (or something like that
We used the Stuff () function to get the date in the field in the right location and now want to 'unstuff' it

Seems simple, but I am running into troubl

Sonja,

[quoted text, click to view]

You can use the substring() and charindex() functions.

create table #Orders (
OrderID int primary key nonclustered,
Ship varchar(60) not null
)
go
-- Make some test data.
insert #Orders
select OrderID, cast('[' +ShipName + ', |' +
isnull(convert(varchar(30), ShippedDate, 101), '') +
'|]'as varchar(60)) Shipped
from Northwind..Orders
go
select OrderID,
substring(Ship, 2, charindex('|', Ship)-4),
cast(nullif(replace(substring(Ship,
charindex('|', Ship)+1, 10), '|]', ''), '')
as smalldatetime)
from #Orders
go
drop table #Orders


You can also create a computed column in the table for the date.

create table #Orders (
OrderID int primary key nonclustered,
Ship varchar(60) not null,
ShipDate as cast(nullif(replace(substring(Ship,
charindex('|', Ship)+1, 10), '|]', ''), '')
as smalldatetime)
)


Linda

Hi Sonja,

In addition to linda's post here are my comments....
I really don't know what forced you to have a table design as what you've shown. But this
is not a normalized database design ,if you are storing a delimited value in it (like
comma). This will lead to several problem and let me note a down a few. A suggested table
design would be to have seperate columns which will seperately store datetime value and
other will be stored in seperate column.

If you are using any indexes on the column, it will not be used because you'll have to use
built-in functions to seperate the values and hence will have an impact on performance.

if in future you want to update a particular date from 12/03/96 to some other value then
how will you do it?? OR what if you want to delete a row from table having a date in
particular range.?
You will have to again take help of some built in function like replace or stuff etc. or
you will have to write a t-sql code to do it (which could have been accomplished through a
simple update statement if you have 2 seperate columns.)

There might be other instances like this as well. In short do not opt for storing values
like this which, will ultimately lead to extra rather complicated coding work and impact
performance of your application largely.


--
- Vishal

Thanks for the input, we will definitely use your suggestions-