[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

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

0
Leo Torres
Asked:
Leo Torres
  • 2
1 Solution
 
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
 
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
 
Leo TorresSQL DeveloperAuthor Commented:
thanks this will do for now..
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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