CODEWITHSUNDEEP
sqlpostgresqlreplaceregexp-replace
Hari
Hari

Reputation: 333

how to replace dots from 2nd occurrence

I have column with software versions. I was trying to remove dot from 2nd occurrence in column, like

select REGEXP_REPLACE('12.5.7.8', '.','');

expected out is 12.578

sample data is here

Is it possible to remove dot from 2nd occurrence

Upvotes: 0

Views: 688

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

One option is to break this into two pieces:

  • Get the first number.
  • Get the rest of the numbers as an array.

Then convert the array to a string with no separator and combine with the first:

select (split_part('12.5.7.8', '.', 1) || '.' || 
        array_to_string((REGEXP_SPLIT_TO_ARRAY('12.5.7.8', '[.]'))[2:], '')
       )

Another option is to replace the first '.' with something else, then get rid of the '.'s and replace the something else with a '|':

select translate(regexp_replace(version, '^([^.]+)[.](.*)$', '\1|\2'), '|.', '.')
from software_version;

Here is a db<>fiddle with the three versions, including the version a_horse_with_no_name mentions in the comment.

Upvotes: 2

Caius Jard
Caius Jard

Reputation: 74605

I'd just take the left and right:

concat(
  left(str, position('.' in str)), 
  replace(right(str, -position('.' in str)), '.', '')
)

For a str of 12.34.56.78, the left gives 12. and the right using a negative position gives 34.56.78 upon which the replace then removes the dots

Upvotes: 0

Related Questions