• Login

DataSoft Corporation

LIKE

LIKE allows pattern matching within character-based column data.

Syntax

WHERE expr [ NOT ] LIKE value

Remarks

The value on the right side of a LIKE expression must be a simple string constant, the USER keyword, or (outside a stored procedure) a dynamic parameter supplied at runtime, indicated by a question mark “?”. Dynamic parameters are not supported within SQL Editor, only in application code.

Use the percent sign “%” in the comparison value as many times as desired to match zero or more characters in the column values. Use the underscore “_” to match any one character. If you need to match one of these wildcard symbols as a literal character, use a back slash in front of the symbol to mark the symbol as a literal character.

Table 44

Special Characters

Character

Purpose

Percent sign “%”

Wildcard: matches zero or more characters.

Underscore “_”

Wildcard: matches any single character.

Back slash “\”

Flags the following wildcard character as a literal character, indicating that you want to match the actual wildcard character itself. To match a back slash, enter two back slashes. For example, to match “%“, the pattern should specify “\%0”.

Two single quotes “‘’”

Two single quotes without a space in between them must be used to match a single quote that is part of the result string. For example, if a row in the database contains the value “Jim’s house,” you can match this pattern by specifying LIKE ‘Jim’’s house’ in the WHERE clause. A double-quote (") is not a special character within the pattern string, and can be used like any letter or digit.

Examples

This example matches all column values that are five characters long and have abc as the middle three characters:

SELECT Building_Name FROM Room WHERE Building_Name LIKE '_abc_'

============ 

This example matches all column values that contain a back slash:

SELECT Building_Name FROM Room where Building_Name LIKE '%\\%

============ 

This example matches all column values except those that begin with a percent sign:

SELECT Building_Name FROM Room where Building_Name NOT LIKE '\%%'

============ 

This example matches all column values that contain one or more single-quotes:

SELECT Building_Name FROM Room where Building_Name LIKE '%''%'

============ 

This example matches all column values where the second character is a double-quote:

SELECT Building_Name FROM Room where Building_Name LIKE '_"%'
Thursday, 18 April 2019 Posted in Pervasive SQL Command
 DataSoft Store  Knowledgebase  Contact Us
 Sitemap  Privacy Policy
    Customer Service


Copyright © 2019 DataSoft Corporation All rights reserved.