Extracting data from each record in a column

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.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 current
ly
> 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 dat
a
> 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 o
f
> 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 receiv
ed
> 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 informatio
n.
> CARRIED
> PW&P-144-96 Mr. Mann Mr. Cathcart be received for informatio
n.
> CARRIED.
> Any help is appreciated.|||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 i
f
null statements? If so, where do I place them?
Thanks again for your help.
"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:
>|||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.
>|||I would basically want to put a 'null' value or blank data into the
corresponding new column if there was no data in the old column to pull.
"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:
>
>|||On Thu, 8 Sep 2005 12:50:02 -0700, Christo wrote:

>I would basically want to put a 'null' value or blank data into the
>corresponding new column if there was no data in the old column to pull.
Hi Christo,
In that case, you'll have to use CASE in each of the assignments in the
SET clause, like this (for brevity, I won't repeat the complete string
manipulation expressions that were in a previous post in this thread)
UPDATE YourTable
SET motion_result = CASE
WHEN Notes LIKE '%MOVED BY%'
THEN -- complicated expression goes here
ELSE NULL
END,
move = CASE
WHEN Notes LIKE '%MOVED BY%SECONDED BY%'
THEN -- complicated expression goes here
ELSE NULL
END,
second_by = CASE
WHEN Notes LIKE '%SECONDED BY%'
THEN -- complicated expression goes here
ELSE NULL
END,
comments = CASE
WHEN Notes LIKE '%SECONDED BY%'
THEN -- complicated expression goes here
ELSE NULL
END
(untested - see www.aspfaq.com/5006 for the steps required to get tested
answers)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)