Length of Substring

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

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
 
appariCommented:
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
 
Anthony PerkinsCommented:
Why not just use REPLACE as in:
SELECT  REPLACE(@str, '%2F', '')
0
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
thanks this will do for now..
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.