CODEWITHSUNDEEP
sqlpostgresql
Logan4200
Logan4200

Reputation: 33

PostgreSQL - Extract string before ending delimiter

I have a column of data that looks like this:

58,0:102,56.00
52,0:58,68
58,110
57,440.00
52,0:58,0:106,6105.95

I need to extract the character before the last delimiter (',').

Using the data above, I want to get:

102
58
58
57
106

Upvotes: 1

Views: 3446

Answers (5)

EMR
EMR

Reputation: 29

This worked for me, I just needed the last field. select substring('abc:def:hij:kef', ':[^:]*$');

substring

:kef (1 row)

Upvotes: -1

Belayer
Belayer

Reputation: 14932

The following treats the source string as an "array of arrays". It seems each data element can be defined as S(x,y) and the overall string as S1:S2:...Sn. The task then becomes to extract x from Sn.

with as_array as 
     ( select string_to_array(S[n], ',') Sn 
         from (select string_to_array(col,':') S
                    , length(regexp_replace(col, '[^:]','','g'))+1  n 
                 from tablename 
              ) t
     )
select Sn[array_length(Sn,1)-1] from as_array

The above extends S(x,y) to S(a,b,...,x,y) the task remains to extracting x from Sn. If it is the case that all original sub-strings S are formatted S(x,y) then the last select reduces to select Sn[1]

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

Might be done with a regular expression in substring(). If you want:
the longest string of only digits before the last comma:

substring(data, '(\d+)\,[^,]*$')

Or you may want:
the string before the last comma (',') that's delimited at the start either by a colon (':') or the start of the string.
Could be another regexp:

substring(data, '([^:]*)\,[^,]*$')

Or this:

reverse(split_part(split_part(reverse(data), ',', 2), ':', 1))

More verbose but typically much faster than a (expensive) regular expression.

db<>fiddle here

Upvotes: 1

forpas
forpas

Reputation: 164224

With a CTE that removes everything after the last comma and then splits the rest into an array:

with cte as (
  select
    regexp_split_to_array(
      replace(left(col, length(col) - position(',' in reverse(col))), ':', ','),
      ','
    ) arr                                             
  from tablename  
)  
select arr[array_upper(arr, 1)] from cte

See the demo.
Results:

| result |
| ------ |
| 102    |
| 58     |
| 58     |
| 57     |
| 106    |

Upvotes: 0

Hambone
Hambone

Reputation: 16417

Can't promise this is the best way to do it, but it is a way to do it:

with splits as (
  select string_to_array(bar, ',') as bar_array
  from foo
),
second_to_last as (
  select
    bar_array[cardinality(bar_array)-1] as field
  from splits
)
select
  field,
  case
    when field like '%:%' then split_part (field, ':', 2)
    else field
  end as last_item
from second_to_last

I went a little overkill on the CTEs, but that was to expose the logic a little better.

Upvotes: 0

Related Questions