Tech Tip: Language differences when testing string Equality Conditions with Wildcards

PRODUCT: 4D | VERSION: 12.3 | PLATFORM: Mac & Win

Published On: December 1, 2011

With the introduction of SQL to the 4D language there are now two ways to filter queries using wildcards when testing string equality, the native 4D language way and the SQL way. They are different and they do not mix.

Consider the example queries below, one using native 4D and on using SQL.

QUERY([People];[People]LastName="Andrews")

Begin SQL
    SELECT *
    FROM People
    WHERE LastName = "Andrews"
    INTO :MyListBox;
End SQL

Focus on how the equality conditions are constructed. Both use the equal sign to specify and exact match for the last name.

It would be reasonable to expect that a wildcard query would be constructed just the same as shown below.

QUERY([People];[People]LastName="a@")

Begin SQL
    SELECT *
    FROM People
    WHERE LastName = "a%"
    INTO :MyListBox;
End SQL

However, in the case of the SQL query the equality condition is not constructed correctly. In SQL the equal sign is used to strictly establish equality of both sides. When conducting a wildcard query using SQL you must use the SQL verb "LIKE." The properly constructed wildcard SQL query is shown below.

Begin SQL
    SELECT *
    FROM People
    WHERE LastName LIKE "a%"
    INTO :MyListBox;
End SQL

Similarly, using the ampersand "&" to construct a logical AND condition or using the vertical bar "|" to construct a logical OR as in the 4D query syntax is incorrect in SQL. Use the SQL keywords "AND" or "OR" to construct logical statements.