Worked perfectly.<br />Thanks Richard.<br /><br /><div class="gmail_quote">On Tue, Jul 1, 2008 at 10:17 AM, Richard
Huxton<<a href="mailto:dev@archonet.com">dev@archonet.com</a>> wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="Ih2E3d">DhanushkaSamarakoon wrote:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204,
204,204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Thanks for the reply.<br /> But one problem I have is I need
toloop through all the rows in the table<br /> and in each iteration I need to fetch the value of mydate in to a
variable<br/> and split it to month and year and add two rows with *value, 91, month* (2 ,<br /> 91, Augest) and
*value,86, year* (2 , 86 , 2009)<br /><br /> So I need an idea on<br /> - how to loop through all the rows thats
returnedby a select statement.<br /> - for each statement how to get the value of mydate in to a variable, so<br />
thatI can use SubString to split it in to date and year and use them in the<br /> insert statement.<br
/></blockquote><br/></div> Ah, looking back I see "mydate" isn't actually a date. Note the space in the pattern for
substring()below:<div class="Ih2E3d"><br /><br /> INSERT INTO metadata (value, field, mydate)<br /></div> SELECT
value,91,substring(mydate, '(.+) ')<br /> FROM metadata<br /> UNION ALL<br /> SELECT value, 86, substring(mydate, '
(.+)')<br/> FROM metadata;<br /><br /> Does that do it for you? Try the SELECT clauses by themselves to check if
they'redoing the right thing.<br /><font color="#888888"><br /> -- <br /> Richard Huxton<br /> Archonet Ltd<br
/></font></blockquote></div><br/>