Working with strings in SQLite

SQLite has a number of functions for manipulating text strings. In this next example the ".import" command was used to load values into the "fish" table from a comma separated values file (*.csv).

sqlite>CREATE TABLE fish(common_name TEXT,latin_name TEXT);
sqlite>.separator ","
sqlite>.import fishlist.csv fish
sqlite>.separator "|"
sqlite> select common_name,latin_name from fish;
common_name|latin_name
'Brown Trout' |'Salmo trutta'
'American Shad' |'Alosa sapidissima'
'Black Bullhead' |'Ictalurus melas'
'Chain Pickerel' |'Esox niger'
'Muskellunge' |'Esox masquinongy'
'Walleye'|'Stizostedion vitreum'

trim(field_name) removes white space characters from both ends of the string.
replace(field_name,'old_string','new_string')

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. We can fix that by using some of the string manipulation functions in SQLite. First, it is advisible to try the changes out with a select query to test the result, before doing any permanent changes to the data.
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.

sqlite> SELECT trim(replace(common_name,'''','')) 'Common Name' ,trim(replace(latin_name,'''','')) 'Scientific Name' from fish;
Common Name|Scientific Name
Brown Trout|Salmo trutta
American Shad|Alosa sapidissima
Black Bullhead|Ictalurus melas
Chain Pickerel|Esox niger
Muskellunge|Esox masquinongy
Walleye|Stizostedion vitreum

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> UPDATE fish SET common_name=trim(replace(common_name,'''','')),latin_name =trim(replace(latin_name,'''',''));
sqlite> select * from fish;
common_name|latin_name
Brown Trout|Salmo trutta
American Shad|Alosa sapidissima
Black Bullhead|Ictalurus melas
Chain Pickerel|Esox niger
Muskellunge|Esox masquinongy
Walleye|Stizostedion vitreum
sqlite>

Using the SUBSTR Function to return parts of a string.

SUBSTR(field_name,start_location)
SUBSTR(field_name,start_location,substring_length )

If the start location is a positive integer then the substring will begin x number of characters from the left of the string. If the start location is a negative integer then the substring will begin x number of characters from the right.

sqlite> SELECT SUBSTR('String Manipulation in SQLite',8,12);
Manipulation
sqlite> SELECT SUBSTR('String Manipulation in SQLite',-9,9);
in SQLite
sqlite> SELECT SUBSTR('String Manipulation in SQLite',-9,2);
in
sqlite> SELECT SUBSTR('String Manipulation in SQLite',8);
Manipulation in SQLite
sqlite> SELECT SUBSTR('String Manipulation in SQLite',-9);
in SQLite
sqlite>

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 ReqNumber,ReqDate FROM ReqEquip Limit 1;

ReqNumber ReqDate
---------- ----------
1000 2007-10-30
sqlite>

sqlite> SELECT ReqNumber,SUBSTR(ReqDate,6,2) 'MONTH', SUBSTR(ReqDate,9,2) 'DAY',SUBSTR(ReqDate,1,4) 'YEAR' FROM ReqEquip;

ReqNumber MONTH DAY YEAR
---------- ----- ----- -----
1000 10 30 2007
1001 11 5 2007
1002 11 6 2007
1003 12 1 2007
sqlite>

Concatenate Strings using the "||"

sqlite> SELECT ReqNumber, SUBSTR(ReqDate,6,2)||'-'||SUBSTR(ReqDate,9,2)||'-'||SUBSTR(ReqDate,1,4) 'Requisition Date' FROM ReqEquip;

ReqNumber Requisition Date
---------- --------------------
1000 10-30-2007
1001 11-05-2007
1002 11-06-2007
1003 12-01-2007

Date and Time Functions

Looking for a date range

SELECT field_1,field_2,date_field WHERE julianday(date_field) BETWEEN julianday('1998-01-01') and julianday('2008-01-01');

Find the number of days between two dates.

sqlite> SELECT julianday('2008-07-03')- julianday('2008-06-20');
13.0

Find Calendar date at a specified interval of time

Plus or minus "days","months", "years"

sqlite> SELECT date('2008-07-03', '+90days');
2008-10-01

Reformat output of Dates with strftime function

%m - Month, %d - Day, %Y - Year

sqlite> SELECT strftime('%m/%d/%Y', '2008-02-02');
02/02/2008


Colin Riley -- December 2007 --