SQL "LIKE"

[househippo]

The wiki give some great examples of basic SQL to ECL code samples. What would be the ECL equivalent to SQL:

Code: Select all

SELECT * FROM `table` WHERE `email` LIKE "%@gmail.com"

[DSC]

There is a standard library function that you can use as a recordset filter to do this:

Code: Select all

IMPORT Std;

DataRec := RECORD
    STRING  email;
END;

myTable := DATASET
    (
        [
            {'one_address@yahoo.com'},
            {'second_address@gmail.com'},
            {'third_address@hotmail.com'}
        ],
        DataRec
    );

r := myTable(Std.Str.EndsWith(email, '@gmail.com'));

OUTPUT(r);

Cheers,

Dan

[DSC]

I just realized that my response was a little specific, focusing on testing the end of a string. Check out the REGEXFIND() built-in function. It would be used in a filter, like the Std.Str.EndsWith() above, and would provide SQL LIKE-like functionality (and more).

Cheers,

Dan

[househippo]

Dan thanks for the response.
Regular expression can be expensive process in terms of CPU and memory when the string are large.
Is there other tools to use besides REGEXFIND() on these large strings when they are MB or GB size?
EX.

Code: Select all

Select * FROM `table` WHERE `html` LIKE "%iPad2%"

[DSC]

Std.Str.Contains() would be another candidate to check out. There are a few others in the string library that may meet your needs as well, such as WildMatch().

Cheers,

Dan

[rtaylor]

househippo,

This previous thread: http://hpccsystems.com/bb/viewtopic.php?f=8&t=1193&p=5185&hilit=+LIKE&sid=45bef542c61fec074e57dfc7f991132c&sid=45bef542c61fec074e57dfc7f991132c#p5185 also addresses the issue of how to do an SQL LIKE.

HTH,

Richard