Solved

Length of Substring

Posted on 2012-03-29
4
402 Views
Last Modified: 2012-03-30
Code below sometimes works my problem is that when the subtraction for length is done sometimes it comes back negative and causes query to fail any ideas..

query below is the same just about is the subtraction I need to fix I need to ensure I at least get 1 or 0 what ever makes it not crash

My issue is updating the table to remove the value  "%2F" from the string
its right after the =5299

Any other suggestion will be great


DECLARE @str VARCHAR(8000)

SET @str = 'BT_XYZ=1&referrerID=60081270&campID=5299%2F&BTData=C0216787861617459544B4BBCB6A9BFA59F9B8A89F6EEF7E9E2D9D6C7DE2722BD22EB8&BT_TRF=118447&BT_WAV=20120322&BT_LNK=10151&BT_EML=5543&BT_VAR=1583'
--SET @str = 'BT_XYZ=1&referrerID=60081270&campID=6299&BTData=C0216787861617459544B4BBCB6A9BFA59F9B8A89F6EEF7E9E2D9D6C7DE2722BD22EB8&BT_TRF=118447&BT_WAV=20120322&BT_LNK=10151&BT_EML=5543&BT_VAR=1583'

PRINT @str

SELECT SUBSTRING(@str,CHARINDEX('&campID=',@str),CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str)-3)


SELECT LEN(SUBSTRING(@str,CHARINDEX('&campID=',@str),CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str)))

SELECT REPLACE(@str,(SUBSTRING(@str,CHARINDEX('&campID=',@str),
			   CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str))),(SUBSTRING(@str,CHARINDEX('&campID=',@str),
			   CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str))))

SELECT LEN(SUBSTRING(@str,CHARINDEX('&campID=',@str),CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str)))

Open in new window

0
Comment
Question by:Leo Torres
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37785164
Why not just use REPLACE as in:
SELECT  REPLACE(@str, '%2F', '')
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 37785272
because that will only remove that value this is a string being created by an application %2F can be any thing if it became 3%D then then replace is no good
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 37785862
are you sure &campID and &BTData are always in the same order as in your sample data?
do you want to delete 3 charcaters before &BTData?
can you post sample data that's causing the error. the data and the sql you posted is not giving any error.  the following SQL is removing the %2F, (Bold part is added by me)


DECLARE @str VARCHAR(8000)

SET @str = 'BT_XYZ=1&referrerID=60081270&campID=5299%2F&BTData=C0216787861617459544B4BBCB6A9BFA59F9B8A89F6EEF7E9E2D9D6C7DE2722BD22EB8&BT_TRF=118447&BT_WAV=20120322&BT_LNK=10151&BT_EML=5543&BT_VAR=1583'
--SET @str = 'BT_XYZ=1&referrerID=60081270&campID=6299&BTData=C0216787861617459544B4BBCB6A9BFA59F9B8A89F6EEF7E9E2D9D6C7DE2722BD22EB8&BT_TRF=118447&BT_WAV=20120322&BT_LNK=10151&BT_EML=5543&BT_VAR=1583'

PRINT @str

SELECT SUBSTRING(@str,CHARINDEX('&campID=',@str),CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str)-3)


SELECT LEN(SUBSTRING(@str,CHARINDEX('&campID=',@str),CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str)))

SELECT REPLACE(@str,(SUBSTRING(@str,CHARINDEX('&campID=',@str),
                     CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str))),(SUBSTRING(@str,CHARINDEX('&campID=',@str),
                     CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str)-3)))

SELECT LEN(SUBSTRING(@str,CHARINDEX('&campID=',@str),CHARINDEX('&BTData=',@str)-CHARINDEX('&campID=',@str)))
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 37787784
thanks this will do for now..
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now