Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I got syntax error at end of the query near ; SQLCODE=-131, ODBC 3 State='42000' I believe the error stems from using ...dba.$Regex_Match(...

declare @excludeSubAcctAliasRegexPatternLst long varchar;
declare @acct_id int;
set @excludeSubAcctAliasRegexPatternLst='test';
set @acct_id=1;
select subAcct_ID, acct_type_short_name 
from dba.subAcct where acct_id = @acct_id
            or  not dba.$Regex_Match(acct_type_short_name, @excludeSubAcctAliasRegexPatternLst);


where dba.$Regex_Match is defined as

ALTER FUNCTION "dba"."$Regex_Match"( in @str2Match long varchar,in @regexPattern long varchar ) 
returns bit not deterministic
external name 'Q:\\SqlAnywhereExtEnv\\SqlAnywhereDotNetDll.dll::SqlAnywhereDotNetDll.Util.regexMatch( string, string) bool' language CLR

and the corresponding clr member is

    public static bool regexMatch(string s, string pattern)
    try { return Regex.IsMatch(s, pattern, roIcEc); }
    catch { }
    return false;

I have used successfully other members from the dll for clr proc. this is my first time using clr function.

asked 28 Aug '14, 01:27

gg99's gravatar image

accept rate: 10%

edited 28 Aug '14, 01:44

Aside: You might also use SQL Anywhere's builtin regular expression support, i.e. SIMILAR TO and/or REGEXP, and note, those can be used as boolean values, i.e.

...or acct_type_short_name NOT SIMILAR TO @excludeSubAcctAliasRegexPatternLst

I guess the .NET RegexOptions can be mapped to according patterns/flags.

(28 Aug '14, 04:49) Volker Barth


I failed to find flags for clr compatiabiilty of regex in silmilar to and regexp for SA 11... ( found the online syntax:

if you wonder why clr regex match, the filter is actually a list of ansi like expression reformatted as regex

(28 Aug '14, 12:22) gg99
Replies hidden

Sorry, my last sentence was misleading. SIMILAR TO and REGEXP do not use flags but I guess the .NET RegexOptions flags can be expressed with according patterns and/or the different behaviour of SIMILAR TO vs. REGEXP - e.g. SIMILAR TO uses the database collation (and therefore will use "ignore case" semantics in a case insensitive database) whereas REGEXP will use case sensitive semantics - cf. this overview from the v11.0.1 "What's New" pages...

(29 Aug '14, 04:11) Volker Barth

I think the error comes from the fact that you treat the bit datatype as a boolean value which is not valid in SQL Anywhere - cf. that sample which also raises -131 (SQLE_SYNTAX_ERROR):

   declare b bit = 0;
   select * from sys.dummy where not b;

In contrast, the following comparison with 0 or 1 will work:

   declare b bit = 0;
   select * from sys.dummy where b = 0;

So I guess you just might have to change the second condition to "...or dba.$Regex_Match(acct_type_short_name, @excludeSubAcctAliasRegexPatternLst) = 0"

FWIW, there's not boolean datatype in SQL Anywhere.

permanent link

answered 28 Aug '14, 03:35

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Comment Text Removed
Comment Text Removed

To restate Volker's correct answer: bool maps to BIT in SQL Anywhere, and BIT is an integer data type, NOT a boolean TRUE/FALSE or TRUE/FALSE/UNKKNOWN datatype. There is no boolean data type for variables in SQL Anywhere. Predicates (comparisons) can return boolean values, of course, but you cannot store or pass around those values.

Perhaps you can code it this way...

or not ( dba.$Regex_Match(acct_type_short_name, @excludeSubAcctAliasRegexPatternLst) = 1 )

if 1 is the true bool value that is mapped to integer bit (I don't know, you tell me :)

permanent link

answered 28 Aug '14, 16:18

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 28 Aug '14, 01:27

question was seen: 2,466 times

last updated: 29 Aug '14, 04:12