Hi There,
I had asked a question related to something like this before but never got to the bottom of it. I am hoping someone can help me this time.
I have a text field with in this field there id information on when a bill date was chaged 'from what' , 'to what'
I want to be able to do a substring on this field to pull out the data needed.
At the moment i am doing the folliowing:
SELECT *,SUBSTRING(history_text,2
98,2)
from customer_history where customer_id = 24829
AND history_text like '%<b>BILLING INFO CHANGE</b>%'
AND history_text like '%Bill Day%'
The text is automatically entered into the database the one problem i am having is if i want to just bring back the date and not the text around it then i want just 2 charcters back, however if the date is from the 1st to the 9th this is throwing the formula out.
For example here are two text fields:
Create table customer_history
(history_id int not null,
customer_id int not null,
date_entered datetime not null,
history_text text not null)
Insert into customer_history values (1004963,24829, '2007-06-07 15:48:42.000','<b>BILLING INFO CHANGE</b><br><table border=1 cellpadding=0 cellspacing=0><tr><td height=3></td></tr><tr><td
width=3></td><td bgcolor=#e6e6e6> </td
><td bgcolor=#e6e6e6>TO</td><td
bgcolor=#e6e6e6>FROM</td><
/tr><tr><t
d width=3></td><td> Bil
l Day </td><td> 25
</td
><td> 
;17 <
/td></tr><
tr><td width=3></td><td> Tax
Exempt Expiration Date </td><td> 0
6/2007&nbs
p;</td><td
> 12/
1999
</td></tr>
</table>')
;
Insert into customer_history values (1002583,12345,'2007-06-07
15:48:42.000','<b>BILLING INFO CHANGE</b><br><table border=1 cellpadding=0 cellspacing=0><tr><td height=3></td></tr><tr><td
width=3></td><td bgcolor=#e6e6e6> </td
><td bgcolor=#e6e6e6>TO</td><td
bgcolor=#e6e6e6>FROM</td><
/tr><tr><t
d width=3></td><td> Bil
l Day </td><td> 7&
nbsp;</td>
<td>
4 </t
d></tr><tr
><td width=3></td><td> Sal
es Rep </td><td> &n
bsp;</td><
td> U
ser, Legacy </td></tr></ta
ble>');
With my syntax i am getting back with on the correct results ie(25) but with the other i am getting back (&n).
can anyone tell me what why to write the syntax to retrieve only the 25 and the 7?
All help appriciated,
Kind Regards,
Elaine
