04-30-2015 02:45 PM
I'm trying to pass a string to a Global Function/UDF that I've been working on. The purpose of the UDF is to take a string, delimited by pipes (|), remove any duplicate values from the string, and return a string without any duplicate values back, also delimited by pipes. The problem I have is that the string returned is always <= 255 characters, when in some instances it should be more than 255. I know that strings in the Expression node automatically default to 255 if there is no explicit size given. I assume that the format of setting the size of a string in a UDF would be similar to the format of setting the size in an Expression node, e.g. String (5000) input, but don't know for sure. Is there a way passed this limitation?
05-01-2015 11:34 AM
You are correct that the in stating that string symbols may be declared with a size. If you assign a value to a string symbol, it is truncated/increated to this size. If you do not specific a size, 255 is used by default.
This is an example from the Expression Reference document for format of setting the string size:
if Age < 20 then
// nested if/else
I hope this helps.
05-04-2015 10:01 AM
Thank you everyone for your quick responses! I didn't clarify my dilemma enough. I am setting the size of my string variable to greater than 255 i.e. (string (5000) longStr) in the expression node, and I'm passing it as a parameter to my UDF. In the UDF I store the parameter in a string that has a size greater than 255. It eventually gets passed into an output string which I declare as having more than 255 characters. It's just when the UDF returns the value, it always returns only 255 characters. I guess it's more a behavior of the UDF than it is the actual expression node. I wasn't sure if I was doing something wrong. I will try the idea of the EXPRESS_MAX_STRENGTH_LENGTH. Thanks again.
05-01-2015 01:27 PM
There is also a system macro that you might want to look into. It's called EXPRESS_MAX_STRING_LENGTH. This specifies the maximum length of strings declared in expression nodes. You could trying specifying a value in the .cfg file or in a node at the beginning of your job. It can be up to 5,242,880 bytes, actually.
So, with this new setting, you could declare a very long string in your expression node as follows:
I think this might help you as well. I use it quite frequently. It's spelled out in the Installation and Configuration guide for the Studio, if you'd like to look into it further.
05-01-2015 01:30 PM
The following page in the Data Management Studio installation guide includes a description of the EXPRESS_MAX_STRING_LENGTH option: