Using the "LIKE" operator in DS Routine

ririr · Post by **ririr** » Fri Jul 09, 2004 1:34 pm

I am trying to find a pattern in the source column and return Null, if the pattern is found.

For Ex: Source Column Record 1 has : "TBADA'
Record 2 has : "ADTBAAD"
Finding Pattern: "TBA"
Result : @NULL

Any help is appreciated!

ds_developer · Post by **ds_developer** » Fri Jul 09, 2004 2:01 pm

I think I understand - you are currently getting a NULL (not found) and you should be getting a hit (found)?

I would use the Index function. Here is some documentation from BASIC.pdf:

Syntax
INDEX (string, substring, occurrence)

Description
Use the INDEX function to return the starting character position for the specified occurrence of substring in string.

Hope this helps,
John

ririr · Post by **ririr** » Fri Jul 09, 2004 3:43 pm

Thanks John, It worked like a charm!

ray.wurlod · Post by **ray.wurlod** » Fri Jul 09, 2004 5:07 pm

The operator in DataStage BASIC is MATCHES rather than LIKE, and it uses a more flexible pattern-matching capability. In your case, all you need is the wild-card pattern element, which is either "..." or "0X".

Code: Select all

InLink.ColumnName MATCHES "...TBA..."

or, for case-insensitive matching

Code: Select all

UpCase(InLink.ColumnName) MATCHES "...TBA..."

When you're ready, find MATCHES in the on-line help or BASIC manual and learn about the other pattern-matching components. For example you can match specific or arbitrary numbers alphabetic or numeric characters.