"Christo" wrote:

> I need to be pointed in the right direction on extracting data.  I currently
> have a clumn that contains a field that holds several different pieces of
> data that I want to put into specific fields.  Examples of the current data
> in the Notes column is;
>
> A&L-97-92 MOVED BY: Mr. Jenkins SECONDED BY: Mr. Holstock boundary changes
> be received ****CARRIED.****
> PW&P-181-92 MOVED BY: Mr. Gifford SECONDED BY: Mr. Moore scholarships be
> awarded ****CARRIED.****
> 93-353 MOVED BY: Mr. Nelson SECONDED BY: Mr. Gifford ban the landfilling of
> Old Corrugated Cardboard ****CARRIED.***
> CW-20-01 MOVED BY: Mr. Cathcart SECONDED BY: Mr. Batten be received for
> information. ****CARRIE
> PW&P-144-96 MOVED BY: Mr. Mann SECONDED BY: Mr. Cathcart be received for
> information. ****CARRIED.****
>
> I would like to seperate this into the following fields;
>
> MOTION          MOVE            SECOND          COMMENTS                    
>   RESULT
> --------------- --------------- ---------------
> ------------------------------ ---------------
> A&L-97-92       Mr. Jenkins     Mr. Holstock    boundary changes be received
>   CARRIED.
> PW&P-181-92     Mr. Gifford     Mr. Moore       scholarships be awarded     
>   CARRIED.
> 93-353          Mr. Nelson      Mr. Gifford     ban the landfilling of Old  
>   CARRIED.
>                                                 Corrugated Cardboard
>
> CW-20-01        Mr. Cathcart    Mr. Batten      be received for information.
>   CARRIED
> PW&P-144-96     Mr. Mann        Mr. Cathcart    be received for information.
>   CARRIED.
>
> Any help is appreciated.

"Uday" wrote:

> Assiming that all the columns are in the same table: test1,
>
> --------------
> Test1 (structure):
> test
> motion_result
> move
> second_by
> comments
> ------------------------
> Assumptions:
> Name in second_by column is always one word (Mr. xxx) - xxx as one word
> There would always be 'MOVED BY:' and 'SECONDED BY:' strings in the source
>
> update test1
> set motion_result = ltrim(rtrim(substring(test, 1, (charindex('MOVED
> BY',TEST) - 1) ))),
> move = ltrim(rtrim(substring(test, (charindex('MOVED BY',TEST) + 9),
> (charindex('SECONDED BY',TEST) - charindex('MOVED BY',TEST) -9 ) ))),
> second_by = ltrim(rtrim(substring(test, (charindex('SECONDED BY',TEST) +
> 12), (charindex(' ', test, (charindex('SECONDED BY',TEST) + 17) ) -
> charindex('SECONDED BY',TEST) - 12)))),
> comments = replace(ltrim(rtrim(substring(test, charindex(' ', test
> ,charindex('SECONDED BY',TEST) + 18), 100))), '*','')
>
> Check and modify according to your needs......
> T-Sql procedure would be easier, but could take time to run....
>
> Hope it helps,
> _Uday
>
> "Christo" wrote:
>
> > I need to be pointed in the right direction on extracting data.  I currently
> > have a clumn that contains a field that holds several different pieces of
> > data that I want to put into specific fields.  Examples of the current data
> > in the Notes column is;
> >
> > A&L-97-92 MOVED BY: Mr. Jenkins SECONDED BY: Mr. Holstock boundary changes
> > be received ****CARRIED.****
> > PW&P-181-92 MOVED BY: Mr. Gifford SECONDED BY: Mr. Moore scholarships be
> > awarded ****CARRIED.****
> > 93-353 MOVED BY: Mr. Nelson SECONDED BY: Mr. Gifford ban the landfilling of
> > Old Corrugated Cardboard ****CARRIED.***
> > CW-20-01 MOVED BY: Mr. Cathcart SECONDED BY: Mr. Batten be received for
> > information. ****CARRIE
> > PW&P-144-96 MOVED BY: Mr. Mann SECONDED BY: Mr. Cathcart be received for
> > information. ****CARRIED.****
> >
> > I would like to seperate this into the following fields;
> >
> > MOTION          MOVE            SECOND          COMMENTS                    
> >   RESULT
> > --------------- --------------- ---------------
> > ------------------------------ ---------------
> > A&L-97-92       Mr. Jenkins     Mr. Holstock    boundary changes be received
> >   CARRIED.
> > PW&P-181-92     Mr. Gifford     Mr. Moore       scholarships be awarded     
> >   CARRIED.
> > 93-353          Mr. Nelson      Mr. Gifford     ban the landfilling of Old  
> >   CARRIED.
> >                                                 Corrugated Cardboard
> >
> > CW-20-01        Mr. Cathcart    Mr. Batten      be received for information.
> >   CARRIED
> > PW&P-144-96     Mr. Mann        Mr. Cathcart    be received for information.
> >   CARRIED.
> >
> > Any help is appreciated.

"Christo" wrote:

> Thanks for the help so far
>
> I presummed all the records were complete but they are not, there are some
> records that are missing Moved and Seconded, as well some missing seconded. 
> There are some records that are even blank.  Is it simple enough to put in if
> null statements? If so, where do I place them?
>
> Thanks again for your help.
>

"Uday" wrote:

> Not sure If I understand your question correctly....
>
> if you add where clause
> where charindex('MOVED BY:', test) <> 0
> and charindex('SECOND BY:', test) <> 0
>
> to update just the good ones..
> _Uday
>
> "Christo" wrote:
>
> > Thanks for the help so far
> >
> > I presummed all the records were complete but they are not, there are some
> > records that are missing Moved and Seconded, as well some missing seconded. 
> > There are some records that are even blank.  Is it simple enough to put in if
> > null statements? If so, where do I place them?
> >
> > Thanks again for your help.
> >
>