Page History
...
Use the PATTERN(regExpr, flags) function. See java.util.regex.Pattern below the example for the list of flags.
Example
Code Block |
---|
INSERT INTO testRegExpr (regexpr)
VALUES (PATTERN('.*ab.*', 0)) |
java.util.regex.Pattern
In the <flags> parameter of the PATTERN(regExpr,flags) the following number constants (and their combinations using an OR operation) can be used:
...
Find text which has numbers inside (<flags> value of 0 means no flag is used)
Code Block |
---|
SELECT teamName
FROM baseball
WHERE teamName=PATTERN('[0-9]',0) |
The function PATTERN in an INSERT statement just gives the possibility of storing regular expression patterns in the database. Futher usage of it in PATTERN is not possible because the first parameter can only be a string constant but not a database field. The following example gives a "Unknown tree node" error:
Code Block |
---|
INSERT INTO baseball(patt)
VALUES (pattern('[0-9]',0))
go
SELECT teamName
FROM baseball
WHERE teamName=pattern(patt,0)
go
-- unknown tree node error |
...
BSON Data Types
...
Use HEXDATA(hexString) for BSON BinData of type 0.
Use UUID(uuidString) for UUID data, i.e. BSON BinData of type 3.
Use MD5(md5String) for MD5 data, i.e. BSON BinData of type 5.
Example
Code Block |
---|
INSERT INTO testBinary (binData, uuidData, md5Data)
VALUES (HEXDATA('616B'), UUID('f81d4fae-7dec-11d0-a765-00a0c91e6bf7'), MD5('79054025255FB1A26E4BC422AEF54EB3')) |
...
Code
Use CODE(jsCode) for a code object that is used to represent JavaScript code.
Example
Code Block |
---|
INSERT INTO testCode (data)
VALUES (CODE('function f() { return 12; }')) |
...
CodeWScope
This data type is not supported in MongoSQL. You can retrieve a CodeWScope field in a SELECT statement, but there is no support to INSERT or UPDATE such values.
...
Use MAXKEY() function for the BSON MaxKey object.
Example
Code Block |
---|
INSERT INTO testMongoTypes (maxkey)
VALUES (MAXKEY()) |
...
MinKey
Use MINKEY() function for the BSON MinKey object.
Example
Code Block |
---|
INSERT INTO testMongoTypes (minkey)
VALUES (MINKEY()) |
...
Configuration Functions
...
This function returns MongoDB version information.
Example
Code Block |
---|
SELECT @@version |
SELECT @@spid
This function returns the server process ID.
Info |
---|
SELECT @@spid command will only work if you are an "admin" user. In MongoDB version 2.2, you need to be able to log in to the "admin" database. In MongoDB version 2.4, you need to have the clusterAdmin role in the "admin" database. |
Example
Code Block |
---|
SELECT @@spid |
SELECT @@current_database
This function returns information on the current database context.
Example
Code Block |
---|
USE test
go
SELECT @@current_database |
SELECT @@username
This function returns the current user.
Example
Code Block |
---|
SELECT @@username |
Utility Statements
...
The EXPLAIN command is used to retrieve a high-level description of the plan that MongoDB uses to implement a specific SQL query. It reports on how the query uses database indices. Users may find the information it reports useful in optimizing database schemas and queries while debugging.
Example
Code Block |
---|
EXPLAIN SELECT *
FROM NewItems |
USE database_name
This statement sets the database context and keeps this context for subsequent statements until the end of the session or another USE statement is encountered.
Example
Code Block |
---|
USE baseball |
See the SHOW Commands page for additional information about commands used to describe database schema objects.
...
EVAL(JavaScript command)
Divbox |
---|
New in 17. |
...
0 |
The EVAL command takes a MongoDB JavaScript command and displays the result set in grid and tree views. Array data is returned in JSON format.
...