CODEWITHSUNDEEP
sqlamazon-redshift
user16462786
user16462786

Reputation: 115

Trimming whatever that comes after a certain symbol and extract a certain number of sections from string

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?

Upvotes: 0

Views: 63

Answers (3)

Data-Science-Link
Data-Science-Link

Reputation: 36

Another option is to use the split_part function. In this function you specify 1. the string of interest, 2. your delimiter, in your case the ".", and 3. which part of the fragmented string you are interested in returning. In your case you will always have four string fragments.

Here is split_part in practice:

SELECT
      '12.34.56.78' AS original_string
    , SPLIT_PART(original_string,'.',1) AS string_fragment_1
    , SPLIT_PART(original_string,'.',2) AS string_fragment_2
    , SPLIT_PART(original_string,'.',3) AS string_fragment_3
    , SPLIT_PART(original_string,'.',1)::varchar || '.' || SPLIT_PART(original_string,'.',2) || '.' || SPLIT_PART(original_string,'.',3) AS final_output.

I believe the last column returns your desired output. Hope this helps!

Upvotes: 2

JamesBData
JamesBData

Reputation: 51

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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

We can try using a regex replacement here:

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

Upvotes: -1

Related Questions