Finding date using PATINDEX in varchar

Hi:
One of the columns in my table is a notes column i.e everytime a user
updates it the date,time, users name and his/her note gets appended in
front. The notes column has text in following format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
I am supposed to filter out the data by each user,its date and time i.e
8/4/2006 Linda King Per MDCR no clm on file,reblld.
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
Any suggestions are welcomed.Hi
DECLARE @.st VARCHAR(100)
SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
SELECT
SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
<sfazili@.gmail.com> wrote in message
news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> Hi:
> One of the columns in my table is a notes column i.e everytime a user
> updates it the date,time, users name and his/her note gets appended in
> front. The notes column has text in following format:
> 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> I am supposed to filter out the data by each user,its date and time i.e
> 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> Any suggestions are welcomed.
>|||Hi Uri:
Thanks for ur reply. Actually the notes field has text in the follwoing
format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
Each update to the notes field just get appended to the field in front.
Firstly, I need to separate each individual update..cant use space as
an identifier... need help here. The thing is that after each
indiviaudal note is identified it should be in follwoing format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
After this need to separate each individaul note into thre columns:
8/4/2006 Linda King Per MDCR/ no clm on file,reblld
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22
3/30/2006 Maria James Patient asks to follow -MEDICARE
Any suggestions appreciated
Uri Dimant wrote:
> Hi
> DECLARE @.st VARCHAR(100)
> SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
> SELECT
> SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
> CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
> SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
>
>
> <sfazili@.gmail.com> wrote in message
> news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> > Hi:
> >
> > One of the columns in my table is a notes column i.e everytime a user
> > updates it the date,time, users name and his/her note gets appended in
> > front. The notes column has text in following format:
> >
> > 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> > 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> >
> > I am supposed to filter out the data by each user,its date and time i.e
> > 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> > 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> >
> > Any suggestions are welcomed.
> >