sqlsqlite

Remove period from specific place

I have a SQLite table with a column num (data type: text)

1.100.00
2.3025.00
2.000.00
3.500.16
4.3124.00

I would like to remove the first period.

The column should then look like this:

1100.00
23025.00
2000.00
3500.16
43124.00

I tried with:

SELECT num, REPLACE(num, substr(num, 2,1),'') FROM log WHERE INSTR(num, '.'),

but it deletes all periods.

How can I remove the period?

Solution
  • You'll want
    SELECT num, substr(num, 1, 1)||substr(num, 3) FROM log WHERE INSTR(num, '.');

    That is: "return the first char pasted with everything from character 3".

    The hickup

    In your original query:

    SELECT num, REPLACE(num, substr(num, 2,1),'') FROM log WHERE INSTR(num, '.');

    The substr(num, 2,1) is first evaluated, not as "position 2" but as "char at position 2", that is: '.'

    Thus your query was internally interpreted as:

    SELECT num, REPLACE(num, '.', '') FROM log WHERE INSTR(num, '.');

    Which, you guess it, meant "replace all dots by nothing".