Jump to content
  • 0

LWSChad

Question

Hi,

I'm stumped.

This code works

Substring([@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2,Charindex('
======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2)

This code breaks with only a minor adjustment: changing + to - at the very end

Substring([@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2,Charindex('
======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) - 2)

This code is a substring in example two that works until I add it to the code above

Charindex('
======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) - 2

 

Any help will be appreciated

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 2

Hi ezIQchad,

You are receiving an error because the Substring function of Caspio doesn't allow a negative value for the length parameter.

Quote

Substring(string, start, length)

Returns a substring starting at the starting position defined by start, with total length defined by length.

Below are two possible solutions to your issue:

Solution 1

Substring(

Substring([@field:ScratchPad],
                                Charindex(': ',[@field:ScratchPad],
                                                Charindex('Departure Airport: ',[@field:ScratchPad])) ,
                                                
                                                                Charindex('======',[@field:ScratchPad],
                                                                                Charindex(': ',[@field:ScratchPad],
                                                                                                Charindex('Departure Airport: ',[@field:ScratchPad])))
                                                                                                
                                - Charindex(': ',[@field:ScratchPad],
                                                Charindex('Departure Airport: ',[@field:ScratchPad])) ) 
,2,len([@field:ScratchPad])
)

This one uses the same logic as your previous code, only now it has another Substring that encapsulates your whole code.

Solution 2

Substring([@field:ScratchPad],
	(Len('Departure Airport: ') + Charindex('Departure Airport: ', [@field:ScratchPad], 0)),	
	(Charindex('=====', [@field:ScratchPad]) - (Len('Departure Airport: ') + Charindex('Departure Airport: ', [@field:ScratchPad], 0)))
)

This second solution acts somewhat like Javascript's slice() method.

 

Both solutions have been tested using different test data, and it looks like they are both returning the same results.

Let me know if you have any questions. I hope this helps.

- G

Link to comment
Share on other sites

  • 0

Thanks!

Those are good options.

I ended up using a workaround using ABSOLUTE.

Substring([@field:ScratchPad],
	Charindex(': ',[@field:ScratchPad],
		Charindex('Passport Date of Issue: ',[@field:ScratchPad])
	) + 2,
	Abs(
		Charindex('===',[@field:ScratchPad],
			Charindex(': ',[@field:ScratchPad],
				Charindex('Passport Date of Issue: ',[@field:ScratchPad])
			)
		) - 
		Charindex(': ',[@field:ScratchPad],
			Charindex('Passport Date of Issue: ',[@field:ScratchPad])
		) - 2
	)
)

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...