Filter Stage - Special character handling

muralisankarr · Post by **muralisankarr** » Mon Dec 28, 2009 9:34 am

Is there any way we have to add the ESCAPE option in Filter Stage Where property. I'm trying to implement the following oracle statement in Filter stage

job_id LIKE 'SA\_%' ESCAPE '\'

How to handle the '_' and '%' character when they are part of search sting?

Many Thanks
MSR

chulett · Post by **chulett** » Mon Dec 28, 2009 9:58 am

What makes you think it supports Oracle syntax? Why don't you spell out in words what it is you are trying to implement?

Akumar1 · Post by **Akumar1** » Mon Dec 28, 2009 10:05 am

parameterize that..

muralisankarr · Post by **muralisankarr** » Mon Dec 28, 2009 11:18 am

chulett wrote:What makes you think it supports Oracle syntax? Why don't you spell out in words what it is you are trying to implement?

I'm not particular about oracle functionality. As Filter stage support LIKE operator on pattern search, just want to know how to go about the meta character (% and _) when they are part of search string. In oracle we can use ESCAPE clause. But is there any way handle the meta characters in datastage LIKE opration.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 28, 2009 3:43 pm

The LIKE operator in a Filter stage uses asterisk (*) as a wildcard. It's all explained in the manual. I don't believe there's any single character match available.

muralisankarr · Post by **muralisankarr** » Mon Dec 28, 2009 6:12 pm

ray.wurlod wrote:The LIKE operator in a Filter stage uses asterisk (*) as a wildcard. It's all explained in the manual. I don't believe there's any single character match available.

The RegEx functionality of filter operator is explained in OEM manuals. When I tested the pattern match with oracle like syntax it worked fine:!: . The oracle type of search yields positive result. But it is not documented. My test condition is to pass the records for which the third character in the job_id column is '_'. The where clause in the filter stage is JOB_ID LIKE '__\_%'