CODEWITHSUNDEEP
sqlstringsqlitesql-updatesubstring
Naiky
Naiky

Reputation: 23

How can I insert a character in the middle of a string in SQLite

I have a column in my DB with some numbers with the following format:

15020
13000
5000
7070
125355
1850000

and I need to update the column values, inserting a ',' before the last two digits. I expect the outcome to be like this:

150,20
130,00
50,00
70,70
1253,55
18500,00

I have tried this:

UPDATE table
SET [column] = replace([column],substr([column],-2),','||substr([column],-2));

but it is not working as I expected, this is what I get in return:

150,20
13,000
5,000
,70,70
1253,55
185,0000

Any help would be apreciated. Thank you

Upvotes: 1

Views: 1416

Answers (2)

forpas
forpas

Reputation: 164079

Use the function SUBSTR().

You can get the part of the string before the last 2 chars with:

SUBSTR([column], 1, LENGTH([column]) -2)

and the last 2 chars with:

SUBSTR([column], -2)

So the update statement should be:

UPDATE tablename
SET [column] = SUBSTR([column], 1, LENGTH([column]) -2) || 
               ',' || 
               SUBSTR([column], -2);

If you have values with 1 or 2 digits and you want a leading 0 before , then:

UPDATE tablename
SET [column] = CASE WHEN LENGTH([column]) <= 2 THEN '0' ELSE '' END ||
               SUBSTR([column], 1, LENGTH([column]) -2) || 
               ',' || 
               SUBSTR([column], -2);

See the demo.

Another way to get the part of the string before the last 2 chars is with an implicit conversion to an integer by an integer division:

UPDATE tablename
SET [column] = ([column] / 100) || ',' || SUBSTR([column], -2);

See the demo.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

One method would use printf():

select printf('%d,%02d', cast(col / 100 int), col % 100)

Here is a db<>fiddle.

In an update, this would simply be:

update t
    set column = select printf('%d,%02d', cast(column / 100 int), column % 100);

Upvotes: 1

Related Questions