I have an output that I need to trim (output from a SQL query.)

The first field is the field of interest, and it may contain a 1, 2, 3, or 4 digit number; it has a comma delimiter, followed by another field that may also be a 1, 2, 3, or 4 digit number, followed by another comma delimiter.

So, when I set up the substring to capture the first field, I need to have it set up for {STRING(Substring|{TASK(c0c680c9-b396-4908-8ec9-2355e474ba4d|StdOut)}|0|4)}. Let's say the current output happens to be 0,0, - both numbers happen to be the same, and both are 1 digit numbers. The only part I want to retain after the trim is the first field, in this case, the number 0.

If I use the trim character function

{STRING(TrimChars|{STRING(Substring|{TASK(c0c680c9-b396-4908-8ec9-2355e474ba4d|StdOut)}|0|4)}|,0)} removes the whole string, not just the ,0, that I hoped to remove (it looks like it removes from both the beginning and the end of the string, so in cases when the two numbers are the same, and they are both one digit numbers, they will likely both be removed.)

Is there a way to set this up to work, using VC functions and variables, so that it will work correctly with any possible combination of numbers for the first and second fields or will this require a script?

Also, I am testing with 0, so I specified "0" in the trim characters - but it would need to be any digit; will it take a range, and if so, what is the correct syntax?

Why do you not use substring instead? I mean, if you know the length of the string from the database.

---

In this case, the length of both first and the second fields vary. The first field is a number that may be one, two, three or four places in length - it's the only field of interest. Since it may be up to four places in length, then I am using a substring that uses a count of four from the startindex of zero. Since the number may be one, two, three or four places in length, then I would normally nest the substring function within a trimcharcter function to remove whatever output appears in cases when the number is a one, two, or three place number. This has worked in cases where the following output is always consistent, say quotes, or spaces, or something that always appears next in the case of a shorter number. However, in this case, the output following the first field also varies, both in content as well as length - it is also a variable number which may be one, two, three, or four places in length.

One key thing is that there is a delimiter between the two numbers - a comma was the chosen delimiter for the SQL output. If it were possible to extract what appears before the first delimiter, regardless of the length of the field, that seems like it would be one possible solution, but so far I haven't found a way to do that with the existing functions within VC. You mentioned using the substring function, but in this case the length of the string is variable.

To be more specific, the job is running a SQL SP that monitors a queue every 15 minutes - it's a number changes each time the job runs - it could be 1, or 100, or 1000 - if we can monitor it with VC, then we can get an alert once it crosses a predefined threshold. The ability to identify specific output by delimiter, not just by length from a startindex, seems like it could be useful in dealing with output from SQL.

I currently see no way. Even if we added a FirstIndexOf function we would run into problems.

What we can do is to add regexp support. Please get the proper regexp for retrieving this value and we can add regexp function to VisualCron and test with your value.

---

\A[0-9]* returns the first variable-length field in comma-delimited numeric output.

Great, we will implement this ASAP.

---

We have added Match and Replace functions for RegExp. Expand the functions in Variables browser in version 5.0.3. We have your example in the RegExp.Match function node.

---

Did you test the new version - did it work as expected?

---

I'm having trouble with functions in general in the new version.

For instance, something that I do often on the VC version we use in production, is to use the substring function, where the string is the output of a task.

So, given this function, where the output being read is 45345,543454,4354,43543,34543345 for

{STRING(Substring,{TASK(b39b2142-8740-4199-8324-462081e5914e,StdOut)},0,4)}

I get an "error in argument" in the value preview pane.

In the case of attempting to substitute the same string to use the regular expression match function to see if it will extract the first numeric field, I enter it like this in the preview pane:

{REGEX(Match|{TASK(b39b2142-8740-4199-8324-462081e5914e,StdOut)}|\A[0-9]*)}

the value preview pane is empty.

Update:

I found that somehow a white space had been added to the beginning of the output that was unexpected. Once that was removed the regex function worked!

However, I still have exactly the same problem with the substring function - I get an "error in argument" in the value preview.

And you are using version 5.0.4. now?

---

We tested the following with success: {STRING(Substring,{STRING(ToUpper,TestString)},0,4)}

What kind of functions fail in 5.0.4?

---

Yes, this test was in 5.0.4.

The specific function was the substring function, when trying to use it with the output of a previous task.

{STRING(Substring,{TASK(b39b2142-8740-4199-8324-462081e5914e,StdOut)},0,4)}

We can't see any direct problems with that. It might be something special about the output. For example, if you are expecting comma (,) in your output you should use pipe (|) in the parameter delimiters like this:

{STRING(Substring|{TASK(b39b2142-8740-4199-8324-462081e5914e|StdOut)}|0|4)}

Otherwise VisualCron might believe that you want to use your output as several parameters.

---

Ah, that makes sense...it's comma-delimited output. Thanks!