postgresqlsyntax-errorcharindex

SQL: Error using charindex when trying to remove all characters after second dot

I have some data that looks like "5823.9.8", these are not dates but amounts of value. What I want to do is to remove the last "." and everything coming after the last dot. I implemented the following code

UPDATE dutch_textile SET total_value_guldens = LEFT(total_value_guldens, CHARINDEX('.', total_value_guldens, CHARINDEX('.', total_value_guldens) + 1) - 1) WHERE CHARINDEX('.', total_value_guldens, CHARINDEX('.', total_value_guldens) + 1) > 0;

I got an error saying the CHARINDEX function does not exist and to test if it was correct or not, I implemented the following code to test it.

UPDATE dutch_textile SET total_value_guldens = LEFT(total_value_guldens, CHARINDEX('.', total_value_guldens) - 1) WHERE CHARINDEX('.', total_value_guldens) > 0;

This code I found here on stackoverflow but I still got the same error.

function charindex(unknown, character varying) does not exist

I have some data that looks like "5823.9.8", these are not dates but amounts of value. What I want to do is to remove the last "." and everything coming after the last dot. But I got the error "function charindex(unknown, character varying) does not exist".

Solution

  • You can use the regexp_replace function to remove the trailing . and any subsequent digits:

    # select regexp_replace('5823.9.8', '\.[0-9]*$', '') as fixed;
 fixed  
════════
 5823.9
(1 row)

    PostgreSQL string functions are documented here.