LIKE, NOT LIKE (U-SQL)

Article
03/30/2017

Summary

U-SQL provides the LIKE and NOT LIKE comparison operators that are familiar from T-SQL that checks if a string value matches or does not match a simple pattern. The pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Note that the comparison is done with culture invariant string comparison using UTF-8 byte-level comparison.

Syntax

LIKE_Expression :=                                                                                       
     string_expression ['NOT'] 'LIKE' Pattern   
     ['ESCAPE' char_expression].

Pattern := 
     string_expression.

Remarks

string_expression
is the expression that creates the string value to be tested.

Pattern
is a string expression that provides the pattern to test against. The pattern in a LIKE can include the following valid wildcard characters.

Wildcard character	Description	Example
%	Any string of zero or more characters.	`WHERE title LIKE '%computer%'` finds all rows with the word 'computer' anywhere in the title.
_ (underscore)	Any single character.	`WHERE fname LIKE '_ean'` finds all rows with four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	`WHERE lname LIKE '[C-P]arsen'` finds all rows with last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range are determined by the culture invariant sort order using UTF-8 byte ordering.
[^]	Any single character not within the specified range ([^a-f]) or set	`WHERE lname LIKE 'de[^l]%'` finds all rows with last names starting with de and where the following letter is not l.

ESCAPE char_expression
specifies optionally a non-null value of type char that can be used to escape any of the wildcard characters. It is put in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.

Return Type

bool

Examples

The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.

@data  = 
    SELECT * FROM 
        (VALUES  
        ("Carsen",      "Engineer"),
        ("Larsen",      "engineer"),
        ("Karsen",      "some%value"),
        ("Barbariol",   "somevalue"),
        ("Barber",      "some_value"),
        ("Olivia",      "someXvalue")
        ) AS T(col1, col2);

// Change "LIKE" to "NOT LIKE" for NOT LIKE examples

@result =
    SELECT * FROM @data
    WHERE col1 LIKE "_arsen";

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like1.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col1 LIKE "[C-K]arsen";

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like2.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col1 LIKE "Barb%";

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like3.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col2 LIKE "Engineer";

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like4.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col2 LIKE "[eE]ngineer";

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like5.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col2 LIKE "some%value";

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like6.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col2 LIKE "some|%value" ESCAPE '|';

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like7.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col2 LIKE "some_value";

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like8.txt" USING Outputters.Tsv();


@result =
    SELECT * FROM @data
    WHERE col2 LIKE "some$_value" ESCAPE '$';

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Like9.txt" USING Outputters.Tsv();