Wed Aug 15, 2018 4:38 pm
Login Register Lost Password? Contact Us


SQL "LIKE"

Comments and questions specific to migrating data from RDBMS

Tue May 27, 2014 8:43 am Change Time Zone

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"
Last edited by househippo on Tue May 27, 2014 4:14 pm, edited 1 time in total.
househippo
 
Posts: 18
Joined: Sun Nov 03, 2013 9:32 pm

Tue May 27, 2014 12:49 pm Change Time Zone

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
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 552
Joined: Tue Oct 18, 2011 4:45 pm

Tue May 27, 2014 9:03 pm Change Time Zone

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
DSC
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 552
Joined: Tue Oct 18, 2011 4:45 pm

Wed May 28, 2014 6:46 am Change Time Zone

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%"
househippo
 
Posts: 18
Joined: Sun Nov 03, 2013 9:32 pm

Wed May 28, 2014 10:31 am Change Time Zone

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
DSC
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 552
Joined: Tue Oct 18, 2011 4:45 pm

Wed May 28, 2014 2:37 pm Change Time Zone

rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1368
Joined: Wed Oct 26, 2011 7:40 pm


Return to From RDBMS

Who is online

Users browsing this forum: No registered users and 1 guest