Monitoring, Management & Location Tracking

Database / SQL query: Pattern matching with the LIKE and ~ operators :-

Aruba Employee

If you're writing a database (SQL) query to match a pattern anywhere within a string, use the LIKE operator and put percent signs (%) before and after the pattern you want to match. (You can also use a tilde operator (~) and leave out the percent signs.)

EXAMPLE:


Set to ignored all rogues where SSID contains the string 'x00':

> UPDATE rogue_ap SET ignored = 1 WHERE ssid LIKE '%x00%';

--OR--

> UPDATE rogue_ap SET ignored = 1 WHERE ssid ~ 'x00';

Here are some other operator combinations you might find useful:

~~ . . . . . . . . . . .same as: LIKE

% (percent): . . . matches any string of zero or more characters

_ (underscore): . matches any single character

ILIKE . . . . . . . . .same as LIKE, but case insensitive

NOT . . . . . . . . . .inverse; matches everything that is NOT matched by LIKE

* . . . . . . . . . . . .case insensitive (same as ILIKE); used with ~ or ~~

! (bang) . . . . . . inverse (same as NOT); used with ~ or ~~

If the pattern does not contain percent signs or an underscore, then the pattern only represents the string itself; in that case LIKE acts like the equals (=) operator.

To match a pattern anywhere within a string, the pattern must therefore start and end with a percent sign (%).

EXAMPLE:

 

On all controllers where value is null, change it to empty string:

 

NOTE: those are two single quotes ( ' ) after the =

> UPDATE device_config SET value = '' WHERE name LIKE '%controller%' AND value IS NULL;

See also
http://www.postgresql.org/docs/7.3/static/functions-matching.html

Version history
Revision #:
1 of 1
Last update:
‎07-01-2014 02:38 AM
Updated by:
 
Labels (1)
Contributors
Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: