• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3260
  • Last Modified:

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>%'

0
Putoch
Asked:
Putoch
  • 3
  • 3
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
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
 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now