Using Patindex substing in t-sql
Posted on 2007-07-26
I am hoping someone can help me here.
I am trying to take certin text out of one text field in a table. This one table holds account history.
By using Substring and Patindex i have been able to see what a customer has moved from and to and put these into two fileds in the select statement. however when i try and do the same to get the price of the product before and after i can not get it to work. I am getting errors:Invalid length parameter passed to the SUBSTRING function.. or no data back altogether.
I have created an example of what i am doing and any sugesstions would be greatly appriciated.
--Create and Insert:
CREATE TABLE Account_history(history_text text not null);
insert into Account_history (history_text) values ('<b>Product</b> changed from <b>BOOK 1</b> to <b>BOOK 2</b><BR><b>Recurring Fee</b> changed from <b>6324.00</b> to <b>3255</b>')
SUBSTRING(history_text, 32, PATINDEX('%</b>%', SUBSTRING(history_text,32,50))+0) AS MoveFrom_Product,--substring passes all the text up to char 32,PATINDEX passes all the wildcards used
WHERE history_text LIKE '<b>Product</b> changed from%to%'
AND history_text NOT LIKE '<b>Product</b> changed from <b></b> to <b>%'