Trimming whatever that comes after a certain symbol and extract a certain number of sections from st-CodePudding

I have a column containing numbers like this '12.34.56.78', '23.45.67.89' etc. and the numbers always come in four sections. However,I am only intersted in the first three sections. If the number of digits in each section is always stable, the solution would be to use SUBSTRING, however, numbers such as '123.456.78.9' is expected as well. Therefore, the only common rule that can be used is the dots. Is there any way to cut off whatever that comes after the third dot?

CodePudding user response：

You should be able to accomplish this like so:

select original_num,
       substring(original_num, 1, (length(original_num) - position('.' in reverse(original_num)))) as new_num
  from a_table;

with an example on DB Fiddle.

CodePudding user response：

We can try using a regex replacement here:

SELECT num, REGEXP_REPLACE(num, '\.\d $', '') AS num_out
FROM yourTable;