Single charcter match along with LIKE

[pandeesh]

Hi,

i need to check for a condition like

Code: Select all

 col1 LIKE a_%

where _ represents a single character.
We can go with index() function if we dont have any single char match there.
How this can be handled in transformer?

Thanks

[ray.wurlod]

With an Index() function. It searches for substrings (specify the substring - you don't need a wildcard character).

[chulett]

Can you be more specific regarding what you mean by "where _ represents a single character"? I'm unclear what the requirement is or what the underscore represents here... it must be a character rather than a number?

Typically when you want to do "like xxx%", meaning the string starts with a certain pattern, you can do a simple substring equality match but I can't tell if that would be appropriate here.

[pandeesh]

Here the pattern I want to scan is a_b%.
It should match the strings like aab%.
The _ can be replaced by Any single character .

[kandyshandy]

Check if this is what you want..

InputString[1,1] = 'a' and InputString[3,2] = 'b%'

or

InputString[1,1] = 'a' and InputString[3,1] = 'b'

[qt_ky]

The original post is somewhat vague it the example looks like SQL, in which the % sign is a wildcard representing zero or more of any character, and also in which the _ is a wildcard representing any one character (standard SQL).

If you want to match on the % sign literally then use Kandy's first example.

If your unspoken rule is to handle % as standard SQL wildcard then use Kandy's second example.

[pandeesh]

Yes kandy. Thats the way I was also thinking about .
But when we have the pattern like %a_b%, I don't know how to match all the possible patterns .

[chulett]

Seems you'd need to fall back to the Matches function for that, which would mean a Server job or a Server Shared Container.

[kandyshandy]

Yes kandy. Thats the way I was also thinking about .
But when we have the pattern like %a_b%, I don't know how to match all the possible patterns .

That is also possible. No time to give you the code.

Find the location of first 'a' using Index, then add 3 to it and see whether the character in that location is 'b'. That's it.

[pandeesh]

It will become some more complex that if the number of a are more in the string and only one pattern is matching .
So we nee to recursively apply for all the occurrence of a .
Thanks for your help all

[kandyshandy]

So we nee to recursively apply for all the occurrence of a

Is this the actual requirement? What i thought is you want to select some string which has one occurrence of %a_b%.

Anyways, i don't think you can achieve this with a cake walk.