Documentation Source Text

Check-in [ec08a46c8b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to the LIKE and GLOB documentation. CVSTrac ticket #3290.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ec08a46c8b7cd3740117c4f30086ae34554c0eb2
User & Date: drh 2008-08-12 18:10:26.000
Context
2008-09-02
13:34
Documentation changes for 3.6.2 (check-in: 208918a3f1 user: drh tags: trunk)
2008-08-12
18:10
Updates to the LIKE and GLOB documentation. CVSTrac ticket #3290. (check-in: ec08a46c8b user: drh tags: trunk)
16:12
Update the FAQ to talk about unicode case conversions and comparisons. (check-in: 668f102220 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
1143
1144
1145
1146
1147
1148
1149

1150
1151
1152
1153
1154
1155
1156
1157
<p>If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. This character may be used in the LIKE pattern
to include literal percent or underscore characters. The escape
character followed by a percent symbol, underscore or itself matches a
literal percent symbol, underscore or escape character in the string,
respectively. The infix LIKE operator is implemented by calling the

user function <a href="#likeFunc"> like(<i>X</i>,<i>Y</i>)</a>.</p>

The LIKE operator is not case sensitive and will match upper case
characters on one side against lower case characters on the other.  
(A bug: SQLite only understands upper/lower case for 7-bit Latin
characters.  Hence the LIKE operator is case sensitive for 8-bit
iso8859 characters or UTF-8 characters.  For example, the expression
<b>'a'&nbsp;LIKE&nbsp;'A'</b> is TRUE but







>
|







1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
<p>If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. This character may be used in the LIKE pattern
to include literal percent or underscore characters. The escape
character followed by a percent symbol, underscore or itself matches a
literal percent symbol, underscore or escape character in the string,
respectively. The infix LIKE operator is implemented by calling the
application-defined SQL functions [like(<i>Y</i>,<i>X</i>)] or
[like(<i>Y</i>,<i>X</i>,<i>Z</i>)]</a>.</p>

The LIKE operator is not case sensitive and will match upper case
characters on one side against lower case characters on the other.  
(A bug: SQLite only understands upper/lower case for 7-bit Latin
characters.  Hence the LIKE operator is case sensitive for 8-bit
iso8859 characters or UTF-8 characters.  For example, the expression
<b>'a'&nbsp;LIKE&nbsp;'A'</b> is TRUE but
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181

<tcl>hd_fragment glob GLOB</tcl>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the function
[GLOB | glob(<i>X</i>,<i>Y</i>)] and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"







|







1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182

<tcl>hd_fragment glob GLOB</tcl>
<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the function
[glob(<i>Y</i>,<i>X</i>)] and can be modified by overriding
that function.</p>

<tcl>hd_fragment regexp REGEXP</tcl>
<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"
1284
1285
1286
1287
1288
1289
1290
1291


1292
1293
1294
1295
1296
1297
1298
  Return a copy of the first non-NULL argument.  If
  all arguments are NULL then NULL is returned.  There must be at least 
  2 arguments.
}

funcdef {glob(X,Y)} {} {
  This function is used to implement the
  "<b>X GLOB Y</b>" syntax of SQLite.


  The [sqlite3_create_function()]
  interface can
  be used to override this function and thereby change the operation
  of the [GLOB] operator.</td>
}

funcdef {ifnull(X,Y)} {} {







|
>
>







1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
  Return a copy of the first non-NULL argument.  If
  all arguments are NULL then NULL is returned.  There must be at least 
  2 arguments.
}

funcdef {glob(X,Y)} {} {
  This function is used to implement the
  "<b>Y GLOB X</b>" syntax of SQLite.
  Note that the X and Y arguments are reversed in the glob() function
  relative to the infix [GLOB] operator.
  The [sqlite3_create_function()]
  interface can
  be used to override this function and thereby change the operation
  of the [GLOB] operator.</td>
}

funcdef {ifnull(X,Y)} {} {
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327

1328
1329
1330
1331
1332
1333
1334
1335
funcdef {length(X)} {} {
  Return the string length of <i>X</i> in characters.
  If SQLite is configured to support UTF-8, then the number of UTF-8
  characters is returned, not the number of bytes.
}

funcdef {like(X,Y) like(X,Y,Z)} {} {
  This function is used to implement the "<b>X LIKE Y &#91;ESCAPE Z&#93;</b>"
  syntax of SQL. If the optional ESCAPE clause is present, then the
  user-function is invoked with three arguments. Otherwise, it is
  invoked with two arguments only. The 

  [sqlite3_create_function()] interface can be used to override this
  function and thereby change the operation of the
  [LIKE] operator. When doing this, it may be important
  to override both the two and three argument versions of the like() 
  function. Otherwise, different code may be called to implement the
  [LIKE] operator depending on whether or not an ESCAPE clause was 
  specified.
}







|


|
>
|







1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
funcdef {length(X)} {} {
  Return the string length of <i>X</i> in characters.
  If SQLite is configured to support UTF-8, then the number of UTF-8
  characters is returned, not the number of bytes.
}

funcdef {like(X,Y) like(X,Y,Z)} {} {
  This function is used to implement the "<b>Y LIKE X &#91;ESCAPE Z&#93;</b>"
  syntax of SQL. If the optional ESCAPE clause is present, then the
  user-function is invoked with three arguments. Otherwise, it is
  invoked with two arguments only. Note that the X and Y parameters are
  reversed in the like() function relative to the infix [LIKE] operator.
  The [sqlite3_create_function()] interface can be used to override this
  function and thereby change the operation of the
  [LIKE] operator. When doing this, it may be important
  to override both the two and three argument versions of the like() 
  function. Otherwise, different code may be called to implement the
  [LIKE] operator depending on whether or not an ESCAPE clause was 
  specified.
}