SQLite ltrim

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

SQLite ltrim() function

Description

The ltrim() function returns a string formed by removing any and all characters that appear in the second argument(N) from the left side of the first argument(M). If the N argument is omitted, ltrim(M) removes spaces from the left side of M.

Syntax :

ltrim(M)
ltrim(M,N)

Argument :

Name Description
M A string whose leading space characters are to be removed.
N optional. character(s) to be removed, if omitted leading spaces to be removed.

Pictorial Presentation

SQLite LTRIM() pictorial presentation

Example-1: SQLite ltrim() function

The following SQLite statement returns the string ‘ Hello’ after removing the leading space characters. In the output, the first column shows the string with leading space characters and the second column shows the string after performing ltrim.

  1. SELECT ' Hello'as "Original String", ltrim(' Hello');  
Original String  ltrim(' Hello')
---------------  ---------------
 Hello           Hello

The above result shows the original string ' Hello' displayed from down the second character of heading 'Original String' but after ltrim it returns just down the first character of the heading 'ltrim(' Hello')'.

Example-2: SQLite ltrim() function

The following SQLite statement returns the string ‘disqualify’ after removing the leading characters 'dis' from the left side

  1. SELECT 'disqualify',ltrim('disqualify','dis');  
'disqualify'  ltrim('disqualify','dis')
------------  ------------------------
disqualify    qualify

The above result shows that the first three characters i.e. 'dis' have removed from the given string.

Example-3: SQLite ltrim() using table

The following SQLite statement will display those rows after left trimming the first_name column by the string 'Al' where the first two characters of the first_name column like 'Al'.

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 employee_id,first_name,last_name,ltrim(first_name,'Al')  
  2. FROM employees  
  3. WHERE first_name like 'Al%';  

Here is the result.

employee_id  first_name  last_name   ltrim(first_name,'Al')
-----------  ----------  ----------  ----------------------
103          Alexander   Hunold      exander
115          Alexander   Khoo        exander
147          Alberto     Errazuriz   berto
158          Allan       McEwen      an
175          Alyssa      Hutton      yssa
185          Alexis      Bull        exis
196          Alana       Walsh       ana

From the above result, it seems that the first_name column indicating by red color have returned after trimming left by the string 'Al' .