Search code examples
postgresqlstring-function

Remove according to the pattern matching

The current raw data : 

1-2-05.11
1-15-05.20

how can I remove after .. The expected result is 1-2-05. I test using split_part and also substring, but the result is not fit the requirement. Any suggestion ?

Solution

  • Try this,I assume that your expected output is 1-2-05.(with .)

    Using split_part().

    SELECT SPLIT_PART('1-2-05.11','.',1)||'.';

    Using substring().

    SELECT SUBSTRING('1-15-05.20', 1, LENGTH('1-15-05.20') - 2)

    • 1 is the starting position(from left) of the string(1-15-05.20) in which substring action to be taken

    • LENGTH('1-15-05.20') - 2, is to define the number of character to be extracted from the given string.The string is 1-15-05.20 the length() of it is 10, you need to remove last two characters from this 10 chars so 10 - 2 ie LENGTH('1-15-05.20') - 2