DSXchange

How can I find multiple strings in a String column.

in sql we can use --

select xyz from abc
where UPPER(DESC) like '%MANGO%ORANGE%'


Read some of the old post here and found that I can use index function to find a string in string field.

Is there any specific function available to search multiple strings ?


In the mean time i can use index function twice to achive my logic..

but what if i have more strings to be searched in one field. More if else ??

Hi swapnilverma,

The index function returns the position of a substring within a string, for your case:

this select:

select xyz from abc
WHERE UPPER (DESC) like '% MANGO% ORANGE%'

you can use the function as follows:

Index (UPPER (DESC) 'MANGO', 1) > 0 and Index (UPPER (DESC) 'ORANGE', 1) > 0

and return the records that matches both substrings

Thanks Saludos,

But I would still like to know if we have any function available to search multiple strings in one string column ...

The only thing I can think of that would do that is matches but the problem is I don't believe it is a valid PX function. You'd have to use a BASIC Transformer or a Server Shared Container to bring that functionality into your job from what I recall.

There's nothing out of the box in the parallel Transformer stage for searching for multiple string. Matches (in a BASIC Transformer stage) would do it. Or you could write your own parallel routine. A probably slower option would be an External Filter stage, that could use regular expressions to report a match/non-match result.

Information Analyzer (8.1.2 or later) has a matches_regex data rule available. It would be interesting to inspect the osh script that is generated from a data rule job that uses this, to see which operator is invoked. Alas, I'm not currently in a position to do that.

Use the dcount function to establish how many occurances there are of your substring e.g. dcount(''dog cat dog cow dog'',"cat") will return 2

That's not quite right---there's only one cat.

DCount counts the number of delimited fields in the first argument based on the delimiter specified in the second argument. There are two fields defined by the delimiter "cat".

I believe the easiest options to implement are the two Index() example given earlier or the Matches function in a BASIC transformer.

Regards,

You can always do it in unix without much stretching. Yes? no? maybe so?

Oooo ooo! [thrusts hand into the air] Pick me! Pick me!

awk?

Somehow, I almost always overlook awk when thinking of a parallel job

AWK yes, i was thinking more like a simple grep

D'oh! [hangs head in shame]

thx guys ...

i have used index function multiple times for now ...

I just came to that this requirement is for "quick fix " and not for the long run ...

thanks again!