Search code examples
sqlstringsqlitetrim

Remove numbers from beginning of strings in a numbered list with SQLite

I have a long numbered list imported into a table, the strings are in the following format:

1. fdhsglahs sdhkgs
2. urgbvdgh ndovh
3. 8yhbnxjghr nvdfo dfhioj
...
9999. vnur neeu nu

I want to remove the numbers in the beginning of the string, the "." adjacent to the number, and any number of spaces that come immediately after the "." and before the next character (that is, before the beginning of the string itself).

Can't find a method to do that in SQLite.

Please notice, some of the strings contain numbers as part of the string, which are not to be removed.

Solution

  • For this requirement you can use string functions like substr(), instr() and ltrim():

    select ltrim(substr(col, instr(col, '.') + 1))
from tablename

    Replace col with the column's name.
    this code returns the part of the string after the . left trimmed of spaces.
    See the demo.

    If you want to update the table:

    update tablename
set col = ltrim(substr(col, instr(col, '.') + 1));

    See the demo.