Solved

Length of Substring

Posted on 2012-03-29
4
404 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

786 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