EXPRESS LIKE or NOT LIKE EXPRESSION

[kayarsenal]

Hi,

I want to express LIKE or NOT LIKE in DataStage. For example I have an SQL statement like this:

select * from Y where X like 'Chels%'
select * from Y where X like '%Arsen%'
select * from Y where X not like '%ManU%'

How will this be represented in DS?

I thought of using MATCH(es)...

I want the where clause in the above SQL statement to be used in the transformer constraint condition...that is the first one would be:

X Matches 'Chels...' ( is it correct? )


the second:

X matches '...Arsen...' (is it correct?)

how would I represent the NOT LIKE though?

Regards,

[chulett]

Simple enough to test in a routine, yes? I believe you've got the syntax correct but for whatever reason I don't use that function all that much. Easy enough to test, however.

The online help should say if there is specific 'not' syntax. If there isn't, you can always use the 'Not' function to reverse the value of any expression.

[DSguru2B]

Or specify the same constraint in one link and create a second link that will be reject link. Any row that doesnt go through the first link will pour down the reject link.

[ray.wurlod]

Pattern matching in server jobs uses neither SQL wildcards, which your examples use, nor regular expressions. Instead they have their own idiosyncratic patterns, which you can read about in the DataStage BASIC manual. To get you started, the "any" wildcard is either "0X" or "..." and the single character wildcard is "1X". You can specify that particular characters in the pattern must be alphabetic or numeric.

Thus X Matches "Chel..." will pick up Chelsea and Cheltenham, X Matches "...Arsen..." will be correct as well. Simply wrap a Matches operator in a Not() function to reverse its effect, for example Not(X Matches "...ManU...")

The pattern can be preceded by a tilde to reverse its effect, for example X Matches "~...ManU..." returns true for any X that does not contain "ManU".