Pattern Matching in Filter Stage

[lshort]

The following statement is valid for SQL (Sybase) to exclude rows whose source_cd column begins with 'abc':

Code: Select all

select * from MYTABLE where source_cd not like 'abc%';

The "where" portion of this statement does not work in the Filter Stage:

Code: Select all

Filter_13: Parse error: Expected comparison, 'between', or 'like' operator, got: "not"
Parse error: Expected boolean operator (and, or) got: "like".

I was under the impression that the Filter Stage excepted a sql where clause.

Anyone know what the deal is here?

[kumar_s]

What is the expression you used in Filter stage?

[MTA]

Did you mention 'abc%'?.
The filter stage expects the exact record value in the where clause. To solve this you might want to create another field in the input record to the filter stage and populate it with a subsrting of the original fileds so that you can specify the exact value as 'abc' in the filter where clause and it should work fine.

[ray.wurlod]

Put on your cynical hat and read the manual again. It says "supports standard SQL expressions, except when comparing strings".
Later, when talking about like, it says "like 'abc' (the second operand must be a regular expression)".
So maybe try using a regular expression as the argument for like.

[lshort]

Thanks everyone for the replies.

Ray. I saw the line about regular expressions. I couldnt find the a valid syntax for "starts with 'abc'".

MTA. your approach is an good one. i may just do it that way if i cant get the regular expression thing to work.

[ray.wurlod]

How about "abc*" which is a UNIX regular expression?

[kumar_s]

"abc*" dosent work, but "abc[a-z][a-z][a-z]..." works.

[lshort]

"abc*" dosent work, but "abc[a-z][a-z][a-z]..." works.

Unfortunately for me the above method requires that i know what come after 'abc' .... alas I do not

[Ultramundane]

This is a regular expression. When using * you the preceding element is used to specify what to apply the * to. In a regular expression a * means zero or more of something. And, a . (period) indicates any character.

Thus, I would expect that
'abc.*' would work. This should/would mean the string that starts with abc and ending in a character any number of times.

Thanks,
Ryan

[Ultramundane]

Unfortunately is does not support extended regular expressions. If it did, the following could have been used to get the not.

source_cd LIKE '!(abc).*'

However, since NOT takes a boolean, you can use
NOT(source_cd LIKE 'abc.*')

Thanks,
Ryan