sql - Remove period from specific place - Stack Overflow

admin管理员组

文章数量:1401666

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

Share Improve this question Follow edited Mar 22 at 11:14 Mark Rotteveel 110k229229 gold badges156156 silver badges224224 bronze badges asked Mar 22 at 11:11 IamBeginnerIamBeginner 3133 bronze badges 1

• 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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 4

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

本文标签： sqlRemove period from specific placeStack Overflow