SQLite lower

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

SQLite lower() function

Description

SQLite lower() converts all the characters in a string to lowercase characters.

The lower(str) function returns a copy of string str with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only.

Syntax :

lower(str)

Argument :

Name Description
str A string whose characters are going to be converted to lowercase.

Pictorial Presentation

SQLite LOWER() pictorial presentation

Example : SQLite LOWER() function

The following SQLite statement returns the given string after converting all of its characters in lowercase.

  1. SELECT LOWER('MY TEST STRING');   

Here is the result.

LOWER('MY TEST STRING')
-----------------------
my test string

Example of LOWER() function on column of a table

The following SQLite statement returns the value of the column email from employees table after converting it from upper to lower case.

Sample table : employees

employee_id  first_name  last_name   email       phone_number  hire_date   job_id      salary      commission_pct  manager_id  department_id
-----------  ----------  ----------  ----------  ------------  ----------  ----------  ----------  --------------  ----------  -------------
100          Steven      King        SKING       515.123.4567  6/17/1987   AD_PRES     24000                                   90
101          Neena       Kochhar     NKOCHHAR    515.123.4568  6/18/1987   AD_VP       17000                       100         90
102          Lex         De Haan     LDEHAAN     515.123.4569  6/19/1987   AD_VP       17000                       100         90
103          Alexander   Hunold      AHUNOLD     590.423.4567  6/20/1987   IT_PROG     9000                        102         60
104          Bruce       Ernst       BERNST      590.423.4568  6/21/1987   IT_PROG     6000                        103         60
105          David       Austin      DAUSTIN     590.423.4569  6/22/1987   IT_PROG     4800                        103         60
106          Valli       Pataballa   VPATABAL    590.423.4560  6/23/1987   IT_PROG     4800                        103         60
107          Diana       Lorentz     DLORENTZ    590.423.5567  6/24/1987   IT_PROG     4200                        103         60
108          Nancy       Greenberg   NGREENBE    515.124.4569  6/25/1987   FI_MGR      12000                       101         100
109          Daniel      Faviet      DFAVIET     515.124.4169  6/26/1987   FI_ACCOUNT  9000                        108         100
110          John        Chen        JCHEN       515.124.4269  6/27/1987   FI_ACCOUNT  8200                        108         100
111          Ismael      Sciarra     ISCIARRA    515.124.4369  6/28/1987   FI_ACCOUNT  7700                        108         100
112          Jose Manue  Urman       JMURMAN     515.124.4469  6/29/1987   FI_ACCOUNT  7800                        108         100
113          Luis        Popp        LPOPP       515.124.4567  6/30/1987   FI_ACCOUNT  6900                        108         100
114          Den         Raphaely    DRAPHEAL    515.127.4561  7/1/1987    PU_MAN      11000                       100         30
115          Alexander   Khoo        AKHOO       515.127.4562  7/2/1987    PU_CLERK    3100                        114         30
116          Shelli      Baida       SBAIDA      515.127.4563  7/3/1987    PU_CLERK    2900                        114         30
117          Sigal       Tobias      STOBIAS     515.127.4564  7/4/1987    PU_CLERK    2800                        114         30
118          Guy         Himuro      GHIMURO     515.127.4565  7/5/1987    PU_CLERK    2600                        114         30
119          Karen       Colmenares  KCOLMENA    515.127.4566  7/6/1987    PU_CLERK    2500                        114         30
120          Matthew     Weiss       MWEISS      650.123.1234  7/7/1987    ST_MAN      8000                        100         50
121          Adam        Fripp       AFRIPP      650.123.2234  7/8/1987    ST_MAN      8200                        100         50
122          Payam       Kaufling    PKAUFLIN    650.123.3234  7/9/1987    ST_MAN      7900                        100         50
123          Shanta      Vollman     SVOLLMAN    650.123.4234  7/10/1987   ST_MAN      6500                        100         50
124          Kevin       Mourgos     KMOURGOS    650.123.5234  7/11/1987   ST_MAN      5800                        100         50
125          Julia       Nayer       JNAYER      650.124.1214  7/12/1987   ST_CLERK    3200                        120         50
126          Irene       Mikkilinen  IMIKKILI    650.124.1224  7/13/1987   ST_CLERK    2700                        120         50
127          James       Landry      JLANDRY     650.124.1334  7/14/1987   ST_CLERK    2400                        120         50
128          Steven      Markle      SMARKLE     650.124.1434  7/15/1987   ST_CLERK    2200                        120         50
129          Laura       Bissot      LBISSOT     650.124.5234  7/16/1987   ST_CLERK    3300                        121         50
130          Mozhe       Atkinson    MATKINSO    650.124.6234  7/17/1987   ST_CLERK    2800                        121         50
131          James       Marlow      JAMRLOW     650.124.7234  7/18/1987   ST_CLERK    2500                        121         50
132          TJ          Olson       TJOLSON     650.124.8234  7/19/1987   ST_CLERK    2100                        121         50
133          Jason       Mallin      JMALLIN     650.127.1934  7/20/1987   ST_CLERK    3300                        122         50
134          Michael     Rogers      MROGERS     650.127.1834  7/21/1987   ST_CLERK    2900                        122         50
135          Ki          Gee         KGEE        650.127.1734  7/22/1987   ST_CLERK    2400                        122         50
136          Hazel       Philtanker  HPHILTAN    650.127.1634  7/23/1987   ST_CLERK    2200                        122         50
137          Renske      Ladwig      RLADWIG     650.121.1234  7/24/1987   ST_CLERK    3600                        123         50
138          Stephen     Stiles      SSTILES     650.121.2034  7/25/1987   ST_CLERK    3200                        123         50
139          John        Seo         JSEO        650.121.2019  7/26/1987   ST_CLERK    2700                        123         50
140          Joshua      Patel       JPATEL      650.121.1834  7/27/1987   ST_CLERK    2500                        123         50
141          Trenna      Rajs        TRAJS       650.121.8009  7/28/1987   ST_CLERK    3500                        124         50
142          Curtis      Davies      CDAVIES     650.121.2994  7/29/1987   ST_CLERK    3100                        124         50
143          Randall     Matos       RMATOS      650.121.2874  7/30/1987   ST_CLERK    2600                        124         50
144          Peter       Vargas      PVARGAS     650.121.2004  7/31/1987   ST_CLERK    2500                        124         50
145          John        Russell     JRUSSEL     011.44.1344.  8/1/1987    SA_MAN      14000       0.4             100         80
146          Karen       Partners    KPARTNER    011.44.1344.  8/2/1987    SA_MAN      13500       0.3             100         80
147          Alberto     Errazuriz   AERRAZUR    011.44.1344.  8/3/1987    SA_MAN      12000       0.3             100         80
148          Gerald      Cambrault   GCAMBRAU    011.44.1344.  8/4/1987    SA_MAN      11000       0.3             100         80
149          Eleni       Zlotkey     EZLOTKEY    011.44.1344.  8/5/1987    SA_MAN      10500       0.2             100         80
150          Peter       Tucker      PTUCKER     011.44.1344.  8/6/1987    SA_REP      10000       0.3             145         80
151          David       Bernstein   DBERNSTE    011.44.1344.  8/7/1987    SA_REP      9500        0.25            145         80
152          Peter       Hall        PHALL       011.44.1344.  8/8/1987    SA_REP      9000        0.25            145         80
153          Christophe  Olsen       COLSEN      011.44.1344.  8/9/1987    SA_REP      8000        0.2             145         80
154          Nanette     Cambrault   NCAMBRAU    011.44.1344.  8/10/1987   SA_REP      7500        0.2             145         80
155          Oliver      Tuvault     OTUVAULT    011.44.1344.  8/11/1987   SA_REP      7000        0.15            145         80
156          Janette     King        JKING       011.44.1345.  8/12/1987   SA_REP      10000       0.35            146         80
157          Patrick     Sully       PSULLY      011.44.1345.  8/13/1987   SA_REP      9500        0.35            146         80
158          Allan       McEwen      AMCEWEN     011.44.1345.  8/14/1987   SA_REP      9000        0.35            146         80
159          Lindsey     Smith       LSMITH      011.44.1345.  8/15/1987   SA_REP      8000        0.3             146         80
160          Louise      Doran       LDORAN      011.44.1345.  8/16/1987   SA_REP      7500        0.3             146         80
161          Sarath      Sewall      SSEWALL     011.44.1345.  8/17/1987   SA_REP      7000        0.25            146         80
162          Clara       Vishney     CVISHNEY    011.44.1346.  8/18/1987   SA_REP      10500       0.25            147         80
163          Danielle    Greene      DGREENE     011.44.1346.  8/19/1987   SA_REP      9500        0.15            147         80
164          Mattea      Marvins     MMARVINS    011.44.1346.  8/20/1987   SA_REP      7200        0.1             147         80
165          David       Lee         DLEE        011.44.1346.  8/21/1987   SA_REP      6800        0.1             147         80
166          Sundar      Ande        SANDE       011.44.1346.  8/22/1987   SA_REP      6400        0.1             147         80
167          Amit        Banda       ABANDA      011.44.1346.  8/23/1987   SA_REP      6200        0.1             147         80
168          Lisa        Ozer        LOZER       011.44.1343.  8/24/1987   SA_REP      11500       0.25            148         80
169          Harrison    Bloom       HBLOOM      011.44.1343.  8/25/1987   SA_REP      10000       0.2             148         80
170          Tayler      Fox         TFOX        011.44.1343.  8/26/1987   SA_REP      9600        0.2             148         80
171          William     Smith       WSMITH      011.44.1343.  8/27/1987   SA_REP      7400        0.15            148         80
172          Elizabeth   Bates       EBATES      011.44.1343.  8/28/1987   SA_REP      7300        0.15            148         80
173          Sundita     Kumar       SKUMAR      011.44.1343.  8/29/1987   SA_REP      6100        0.1             148         80
174          Ellen       Abel        EABEL       011.44.1644.  8/30/1987   SA_REP      11000       0.3             149         80
175          Alyssa      Hutton      AHUTTON     011.44.1644.  8/31/1987   SA_REP      8800        0.25            149         80
176          Jonathon    Taylor      JTAYLOR     011.44.1644.  9/1/1987    SA_REP      8600        0.2             149         80
177          Jack        Livingston  JLIVINGS    011.44.1644.  9/2/1987    SA_REP      8400        0.2             149         80
178          Kimberely   Grant       KGRANT      011.44.1644.  9/3/1987    SA_REP      7000        0.15            149
179          Charles     Johnson     CJOHNSON    011.44.1644.  9/4/1987    SA_REP      6200        0.1             149         80
180          Winston     Taylor      WTAYLOR     650.507.9876  9/5/1987    SH_CLERK    3200                        120         50
181          Jean        Fleaur      JFLEAUR     650.507.9877  9/6/1987    SH_CLERK    3100                        120         50
182          Martha      Sullivan    MSULLIVA    650.507.9878  9/7/1987    SH_CLERK    2500                        120         50
183          Girard      Geoni       GGEONI      650.507.9879  9/8/1987    SH_CLERK    2800                        120         50
184          Nandita     Sarchand    NSARCHAN    650.509.1876  9/9/1987    SH_CLERK    4200                        121         50
185          Alexis      Bull        ABULL       650.509.2876  9/10/1987   SH_CLERK    4100                        121         50
186          Julia       Dellinger   JDELLING    650.509.3876  9/11/1987   SH_CLERK    3400                        121         50
187          Anthony     Cabrio      ACABRIO     650.509.4876  9/12/1987   SH_CLERK    3000                        121         50
188          Kelly       Chung       KCHUNG      650.505.1876  9/13/1987   SH_CLERK    3800                        122         50
189          Jennifer    Dilly       JDILLY      650.505.2876  9/14/1987   SH_CLERK    3600                        122         50
190          Timothy     Gates       TGATES      650.505.3876  9/15/1987   SH_CLERK    2900                        122         50
191          Randall     Perkins     RPERKINS    650.505.4876  9/16/1987   SH_CLERK    2500                        122         50
192          Sarah       Bell        SBELL       650.501.1876  9/17/1987   SH_CLERK    4000                        123         50
193          Britney     Everett     BEVERETT    650.501.2876  9/18/1987   SH_CLERK    3900                        123         50
194          Samuel      McCain      SMCCAIN     650.501.3876  9/19/1987   SH_CLERK    3200                        123         50
195          Vance       Jones       VJONES      650.501.4876  9/20/1987   SH_CLERK    2800                        123         50
196          Alana       Walsh       AWALSH      650.507.9811  9/21/1987   SH_CLERK    3100                        124         50
197          Kevin       Feeney      KFEENEY     650.507.9822  9/22/1987   SH_CLERK    3000                        124         50
198          Donald      OConnell    DOCONNEL    650.507.9833  9/23/1987   SH_CLERK    2600                        124         50
199          Douglas     Grant       DGRANT      650.507.9844  9/24/1987   SH_CLERK    2600                        124         50
200          Jennifer    Whalen      JWHALEN     515.123.4444  9/25/1987   AD_ASST     4400                        101         10
201          Michael     Hartstein   MHARTSTE    515.123.5555  9/26/1987   MK_MAN      13000                       100         20
202          Pat         Fay         PFAY        603.123.6666  9/27/1987   MK_REP      6000                        201         20
203          Susan       Mavris      SMAVRIS     515.123.7777  9/28/1987   HR_REP      6500                        101         40
204          Hermann     Baer        HBAER       515.123.8888  9/29/1987   PR_REP      10000                       101         70
205          Shelley     Higgins     SHIGGINS    515.123.8080  9/30/1987   AC_MGR      12000                       101         110
206          William     Gietz       WGIETZ      515.123.8181  10/1/1987   AC_ACCOUNT  8300                        205         110
  1. SELECT email,lower(email)   
  2. FROM employees  
  3. LIMIT 10;  

Here is the result. Here we have displayed the result only 10 rows as a sample result.

email       lower(email)
----------  ------------
SKING       sking
NKOCHHAR    nkochhar
LDEHAAN     ldehaan
AHUNOLD     ahunold
BERNST      bernst
DAUSTIN     daustin
VPATABAL    vpatabal
DLORENTZ    dlorentz
NGREENBE    ngreenbe
DFAVIET     dfaviet