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?

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?

• sql
• sqlite

• Will the start always be a single digit, then a period character? Will at always be a period followed by two digits at the end? Will it always be a total of exactly 2 periods? If the answer is no to any of these questions, add more sample data having different patterns. – jarlh Commented Mar 22 at 19:35

2 Answers 2

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".

If and only if your string always ends with a dot and two digits you could take the route via a numeric that you then format to the expected output

SELECT num, printf("%.02f", CAST(REPLACE(num, '.', '') as Decimal)/100.0)
FROM log