SQLite length

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

SQLite length() function

Description

For a string value str, the length(str) function returns the number of characters (not bytes) in str prior to the first NUL character.

Since SQLite strings do not normally contain NUL characters, the length(str) function will usually return the total number of characters in the string str. For a blob value str, length(str) returns the number of bytes in the blob. If str is NULL then length(str) is NULL. If str is numeric then length(str) returns the length of a string representation of str.

Syntax :

length(str)

Arguments :

Name Description
str A string whose length is to be returned.

Pictorial Presentation

SQLite LENGTH() pictorial presentation

Example of SQLite LENGTH() function

The following SQLite statement returns the pub_name and length of pub_id from publisher table.

Sample table : publisher

pub_id pub_name pub_city country country_office no_of_branch estd
P001 Jex Max Publication New York USA New York 15 1969-12-25
P002 BPP Publication Mumbai India New Delhi 10 1985-10-01
P003 New Harrold Publication Adelaide Australia Sydney 6 1975-09-05
P004 Ultra Press Inc. London UK London 8 1948-07-10
P005 Mountain Publication Houstan USA Sun Diego 25 1975-01-01
P006 Summer Night Publication New York USA Atlanta 10 1990-12-10
P007 Pieterson Grp. of Publishers Cambridge UK London 6 1950-07-15
P008 Novel Publisher Ltd. New Delhi India Bangalore 10 2000-01-01
  1. SELECT pub_id,LENGTH(pub_name)   
  2. FROM publisher;  
pub_id      LENGTH(pub_name)
----------  ----------------
P001        19
P002        15
P003        23
P004        16
P005        20
P006        24
P007        28
P008        20

Example of SQLite LENGTH() function with where clause

The following SQLite statement returns the pub_name and length of pub_name from publisher table who have the length of there is more than or equal to 20.

Sample table : publisher

pub_id pub_name pub_city country country_office no_of_branch estd
P001 Jex Max Publication New York USA New York 15 1969-12-25
P002 BPP Publication Mumbai India New Delhi 10 1985-10-01
P003 New Harrold Publication Adelaide Australia Sydney 6 1975-09-05
P004 Ultra Press Inc. London UK London 8 1948-07-10
P005 Mountain Publication Houstan USA Sun Diego 25 1975-01-01
P006 Summer Night Publication New York USA Atlanta 10 1990-12-10
P007 Pieterson Grp. of Publishers Cambridge UK London 6 1950-07-15
P008 Novel Publisher Ltd. New Delhi India Bangalore 10 2000-01-01
  1. SELECT pub_name,LENGTH(pub_name)   
  2. FROM publisher  
  3. WHERE LENGTH(pub_name)>=20;   
pub_id      LENGTH(pub_name)
----------  ----------------
P003        23
P005        20
P006        24
P007        28
P008        20