Paul Thorton wrote:
I'm trying to search a table for records whose IP Address field matches an IP Address Regular expression. The field is a UTF-8 VARCHAR(64). The regular expression is
\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b
The following query runs ok (it doesn't return any hits, but I need to figure that out later):
select * from subnets where subnet similar to
'\b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b'
If I put the regular expression in a parameter and run it again, I get the following error.
Dynamic SQL Error SQL error code = -303 arithmetic exception, numeric overflow, or string truncation string right truncation
I've run into this error a few times when searching for values that are longer than the field, although I still don't really understand why the error needs to occur. However, when using regular expressions, the parameter can of course be much longer than the length of the field and still be a valid match. Is this a bug? If not, how do I search using regular expressions as parameters?
Pavel Císař answers:
The problem is that engine (by default) initializes the xsqlvar structure for input parameters according to column length they're associated with. In this case it's subnet column. Communication libraries honour this, and the result is an error you saw. The similar to pattern values is not the sole case one can fall to this. However, there is easy workaround: use CAST function. For example:
select * from subnets where subnet similar to cast(? as varchar(500))
When parameter is used in CAST, the max. length is initialized to length of result value.