This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English version of the page.

Note: This page has been translated by MathWorks. Click here to see
To view all translated materials including this page, select Country from the country navigator on the bottom of this page.

Create Queries with Characters and Variables

The following examples show how to create queries using a date, text, MATLAB® variable, and special characters. Construct these queries using the command line.

Create Query Using Date

This example shows how to format a date in an SQL query.

To write an SQL statement that selects data from your database using a date, format the date according to your database specifications. Consult your database documentation for the correct formatting. This example shows date formatting for an Oracle® database.

Create the database connection conn to the Oracle database using an ODBC driver. The following code assumes that you are connecting to a data source named Oracle with the user name username and password pwd.

conn = database('Oracle','username','pwd');

Create an SQL query sqlquery that contains the full query. Execute the query using conn. The following code uses the table TEST_TYPES and the columns TEST_DT1 and TEST_DT2. The WHERE clause contains Oracle SQL code for filtering the records based on the date. The TEST_DT1 column data type is an Oracle date type. Filter records for dates after June 9, 2013 using the TEST_DT1 column. To convert the date to an Oracle date type, enter the date in the Oracle function to_date. For the date '2013-06-09', specify the format as 'YYYY-MM-DD', one of the date formats in Oracle. Consult your Oracle documentation for alternatives.

Import the data using the SQL query and the fetch function, and display the rows of the results. The query returns the records where the date in the column TEST_DT1 is after June 9, 2013.

sqlquery = ['SELECT * FROM TEST_TYPES ' ...
    'WHERE TEST_DT1 > to_date(''2013-06-09'',''YYYY-MM-DD'')'];
data = fetch(conn,sqlquery)
data = 

  2×2 table

               TEST_DT1                       TEST_DT2
    ____________________________    ____________________________
    '2013-06-10 15:11:00.000000'    '2013-06-10 15:11:22.500000'
    '2013-06-10 15:13:00.000000'    '2013-06-10 15:13:21.870003'

Close the database connection.

close(conn)

Create Query Using Text

This example shows how to include text in your SQL query using a Microsoft® Access™ database.

Create the database connection conn to the Microsoft Access database using an ODBC driver. The following code assumes that you are connecting to a data source named dbdemo with a blank user name and password.

conn = database('dbdemo','','');

Select all records from the table producttable with the product description 'Slinky'. Create an SQL query sqlquery that embeds the product description into the SQL query by using an extra pair of single quotes.

sqlquery = ['SELECT * FROM producttable ' ...
    'where productdescription = ''Slinky'''];

Or, you can write the SQL query as a concatenation of two character vectors using brackets: ['SELECT * FROM producttable where productdescription = ' '''Slinky''']

Execute the SQL query using the fetch function, and import and display the data. data contains the product record with the product description 'Slinky'.

data = fetch(conn,sqlquery)
data = 

  1×5 table

    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          3           4.01e+05           1009            17            'Slinky'     

Close the database connection.

close(conn)

Create Query Using MATLAB Variable

This example shows how to include a MATLAB variable in your SQL query and uses a Microsoft SQL Server® database. To create a connection to this database, you must configure a JDBC data source. For more information, see the configureJDBCDataSource function.

Create the database connection conn to the Microsoft SQL Server database using a JDBC data source without operating system authentication. Specify the data source name, user name, and password.

datasource = "dbname";
username = "username";
password = "pwd"; 
conn = database(datasource,username,password);

To select all invoice data for the first product, create a MATLAB variable productID and set it to the first product number.

productId = 1;

Select all records from the table invoice where the product number is equal to the first product. Create an SQL query sqlquery that concatenates the SQL query with the MATLAB variable productID by using brackets. productID is a numeric variable, but the SQL query is a character vector. Therefore, convert the number to a character vector by using the num2str function.

sqlquery = ['SELECT * FROM invoice ' ...
    'where ProductNumber = ' num2str(productId)];

Execute the SQL query using the fetch function, and import and display the data. data contains the invoice data record for the first product.

data = fetch(conn,sqlquery)
data =

  1×5 table

    InvoiceNumber           InvoiceDate           ProductNumber    Paid          Receipt     
    _____________    _________________________    _____________    _____    _________________

        2101         '2010-08-01 00:00:00.000'          1          false    [1948410×1 uint8]

Close the database connection.

close(conn)

Create Query Using Special Characters

This example shows how to write an SQL query for table names and columns names with special characters. These characters require using escape characters that are specific to your database. Consult your database documentation for the right escape characters. This example uses a Microsoft SQL Server database. To create a connection to this database, you must configure a JDBC data source. For more information, see the configureJDBCDataSource function.

Create the database connection conn to the Microsoft SQL Server database using a JDBC data source without operating system authentication. Specify the data source name, user name, and password.

datasource = "dbname";
username = "username";
password = "pwd"; 
conn = database(datasource,username,password);

This example selects all data in a column whose name contains spaces. This column is in a table whose name also contains spaces. A space is a special character. To select data from this column, you must enclose the spaces with escape characters so that the SQL query executes successfully. Brackets are the escape characters for a Microsoft SQL Server database.

Create an SQL query that contains the column name and table name enclosed in brackets.

sqlquery = 'SELECT [column with spaces] FROM [table with spaces]';

Execute the SQL query using the fetch function, and import and display the data.

data = fetch(conn,sqlquery)
data = 

  2×1 table

        Data       
     ___________    

     'some text'
     'some text'

Close the database connection.

close(conn)

See Also

| | |

Related Topics

External Websites