This tech tip demonstrates how to use the LIKE Operator in a 4D SQL block. The LIKE operator is used in the WHERE clause to search for a specified pattern inside a column.

SELECT * FROM Customers WHERE Name LIKE ‘Tim’; ---This will select all customers named Tim.

Typically, the LIKE operator is accompanied by the wildcard sign ‘%’. Placing wildcards before and after a string will allow us to diversify the search.

SELECT * FROM Customers WHERE Name LIKE ‘%Tim%’; ---This will select all customers’ name that contain Tim. (Tim, Timmy, Timothy, etc.)

To display only the names that start with T, place the wildcard character at the end.

SELECT * FROM Customers WHERE Name LIKE ‘T%’;

In order to include 4D variables surrounded by wildcards, concatenate wildcard characters into the 4D variable first before beginning the SQL block. Assume sVar is an enterable string variable inside a form. Type in a name into sVar, and it will be used to search for records in a listBox called LB1. sVar can search for records using string comparison:

Begin SQL   SELECT * FROM Customers   WHERE Name = :sVar   INTO :LB1; End SQL

This however will only search for records where Name matches sVar exactly. Using wildcards and the LIKE operator will allow us to search for all records that contain sVar. To do this, use the following code:

C_TEXT(sStr) sStr:= ”%” + sVar + “%” Begin SQL   SELECT * FROM Customers   WHERE Name LIKE :sStr   INTO :LB1; End SQL