Need syntax for "matches regex"

[dschaefer]

Hello,

I'm trying to filter a database reference using the "matches regex" criteria. I can't figure out how to get this feature to work. I've tried enclosing my regular expressions in single quotes, double quotes, forward slashes, and so on, but nothing seems to work. I get an error message that says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MATCH_RGX '([A-Z]{8})' at line 1.

I use regular expressions quite regularly (pardon the pun) in JavaScript, PHP, Ruby, etc. Each language has its own quirky rules, and I usually figure it out after a few minutes. But I admit, I'm completely stumped here. I even tried matching on a wild card like this: /.*/ but still, it keeps giving me the above error message. The only thing that doesn't seem to give an error message is to leave the field completely blank. Obviously, that doesn't do much good.

I've Googled and searched the forums, but I've found no definitive guidance on how to use this feature.

Thanks,

Dan

[Subhajit1]

Can you paste your piece of code here.....that way it would be possible for people to understand whats wrong

[dschaefer]

There is no code ... I'm using the GUI and attempting to set up a Variable within a Record Producer as follows:

1. Select: Service Catalog -> Record Producers

2. Set up or edit a Record Producer

3. Under the Variables tab (at the bottom), create or edit a variable

4. Within the Variable page, set Type to Reference

5. Under the Type Specifications tab, select a table to reference, with a Simple reference qualifier

6. Select a field (e.g.; "Name") within the referenced table, then select "matches regex"

7. Enter a regex expression

I cannot find any documentation regarding the format of the regex expression. I started off using standard "slash" notation, then tried different delimiters (including no delimiters at all), but to no avail.

For example:

/[A-Z]{8}/

[A-Z]{8}

%[A-Z]{8}%

I've even tried ... /.*/ or simply .*

The only thing that works is a blank entry, which allows everything through the filter.

What am I missing?

Thanks,

Dan

[Janel2]

I ran into that same situation this week.   You cannot use a regex match on a reference field even though that is an option when setting the condition on the variable.  

[dschaefer]

Thanks Janel. I recently arrived at the same conclusion.

SN should disable this option if it can't be used. I wasted 1+ hours trying to figure it out. 😕