SELECT field_1, field_2, date_field WHERE julianday( date_field) BETWEEN julianday(' ') and julianday(' ') įind the number of days between two dates. Sqlite> SELECT ReqNumber, SUBSTR(ReqDate,6,2)||'-'||SUBSTR(ReqDate,9,2)||'-'||SUBSTR(ReqDate,1,4) 'Requisition Date' FROM ReqEquip ĭate and Time Functions Looking for a date range Sqlite> SELECT ReqNumber,SUBSTR(ReqDate,6,2) 'MONTH', SUBSTR(ReqDate,9,2) 'DAY',SUBSTR(ReqDate,1,4) 'YEAR' FROM ReqEquip Sqlite> SELECT ReqNumber,ReqDate FROM ReqEquip Limit 1 Given a date string in the format of yyyy-mm-dd we can split it into month, day and year with the SUBSTR function Sqlite> SELECT SUBSTR('String Manipulation in SQLite',-9) Sqlite> SELECT SUBSTR('String Manipulation in SQLite',8) Sqlite> SELECT SUBSTR('String Manipulation in SQLite',-9,2) Sqlite> SELECT SUBSTR('String Manipulation in SQLite',-9,9) Sqlite> SELECT SUBSTR('String Manipulation in SQLite',8,12) If the start location is a negative integer then the substring will begin x number of characters from the right. If the start location is a positive integer then the substring will begin x number of characters from the left of the string. SELECT substring ('Dolemite', -3) Result: ite Here’s another example, this time I specify the length of the substring. When you do this, the first character of the substring is found by counting from the right rather than the left. SUBSTR( field_name, start_location, substring_length ) lemite Negative Starting Point You can provide a negative value for the second argument. Using the SUBSTR Function to return parts of a string. Sqlite> UPDATE fish SET common_name=trim(replace(common_name,'''','')),latin_name =trim(replace(latin_name,'''','')) Generally single quotes only are used to enclose string values but the single quote is the character that we wish to replace so this example requires that the single quote to be escaped with 4 single quotes together. Sqlite> SELECT trim(replace(common_name,'''','')) 'Common Name' ,trim(replace(latin_name,'''','')) 'Scientific Name' from fish In the example below, the replace function which is used to remove the quotation marks is nested inside a trim function in order to remove the excess whitespace from outside of the quotation marks. We can fix that by using some of the string manipulation functions in SQLite.įirst, it is advisible to try the changes out with a select query to test the result, before doing any permanent changes to the data. Unfortunately the values were enclosed in single quotes and the ".import" command included them as well as some unwanted white space as part of each string. Replace( field_name,' old_string',' new_string') Trim( field_name) removes white space characters from both ends of the string. Sqlite> select common_name,latin_name from fish Sqlite>CREATE TABLE fish(common_name TEXT,latin_name TEXT) In this next example the ".import"Ĭommand was used to load values into the "fish" table from a comma separated values file (*.csv). SQLite has a number of functions for manipulating text strings.
0 Comments
Leave a Reply. |