Using Patindex substing in t-sql

Hi,
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>')
--Statement:
select
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
      SUBSTRING(history_text,32+PATINDEX('%<b>%',SUBSTRING(history_text,32,50))+2,
 PATINDEX('%</b>%',SUBSTRING(history_text,32+PATINDEX('%<b>%',SUBSTRING(history_text,32,50))+2,500))-1)AS MovedTo_product
 FROM Account_history
WHERE history_text LIKE '<b>Product</b> changed from%to%'
      AND history_text NOT LIKE '<b>Product</b> changed from <b></b> to <b>%'

PutochAsked:
Who is Participating?
 
patriktCommented:
It is the -1 or -2 problem. See, if PATINDEX founs nothing it returns 0 and 0-1=-1 <= Error lengh.

Do the test put 0 instead of 1. It will give bad result but will shouw you where error is.
0
 
patriktCommented:
You are expecting history_text to be at least 50 chars. What if it is not?
As test use first simple query for history_text and see what data you will get. I bet that there is one or more "bad" rows which break your rules.

Test this:
SELECT history_text,LEN(history_text)

FROM Account_history
WHERE history_text LIKE '<b>Product</b> changed from%to%'
      AND history_text NOT LIKE '<b>Product</b> changed from <b></b> to <b>%'
AND LEN(history_text) <50
0
 
PutochAuthor Commented:
You see the CRM we use has an automatic text type that is inserted into this colunm so all colunm lenghs will be the same. This is how i know if i use 32,50 i will get exact lenghts.

I tried to do the same to find the price by first adding in a second table (account_history2) and using the
''<b>Recurring Fee</b>'' as the text in the patindex but this did not work either?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
patriktCommented:
In last part
PATINDEX('%</b>%',SUBSTRING(history_text,32+PATINDEX('%<b>%',SUBSTRING(history_text,32,50))+2,500))    -1    )
When PATINDEX not found it returns 0 and 0-1 is your error source.
0
 
PutochAuthor Commented:
Sorry, i don't really understand.
I have never had a problem with using this before. The select statement i posted orignal works fine,my problem is when i try to pull another two colunms in the select statemtnnt. One to show the Old price and one colunm to show the new price.

I have tried:
SUBSTRING(ah.history_text, 32, PATINDEX('%Fee<%', SUBSTRING(ah.history_text,32,50))-2) AS OLD_PRICE,

,SUBSTRING(ah.history_text, 150, PATINDEX('%</b>%', SUBSTRING(ah.history_text,150,155))-1) AS New_PRICE

but this brought back the Invalid length parameter passed to the SUBSTRING function.. or no data back altogether ERROR ?.
0
 
PutochAuthor Commented:
Hi,
Ok, i tried replacing the 0 with 1 and now the query runs but with no result in that colunm for new price?and no error indicating where the error is?
I have reverted to not using Patindex in finding the Price colunm and i am going to use a correlated subquery. This is bringing me back the results i want anyways,

however thank you for you help with this.
Kind Regards,
Putoch
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.