Create new string from existing one using regex in Postgresql

Question

I am using postgresql as database system (v 9.6.5). I have a table T with a column code. I want to add a new column sorting_code. sorting_code is an another way to write the code.

code is a combination of (in this order) characters, numbers and +, e.g 12e+, 234rr++ or 9999mo are examples of codes. In regex, my code is something like:

[0-9]+[a-z]*\+* (only numbers are required)

What I want to do for my column sorting_code is to transform my code like that:

12e+ => 000120000e0000+

234rr++ => 00234000rr000++

9999mo => 09999000mo00000

In other words, each part (characters, numbers and +) must be exactly 5 characters long. Missing characters being replaced by 0.

I have already a lot of rows in my table T with code. What could be my psql request to create my new column sorting_code?

Thank you

Hambone · Accepted Answer · 2017-11-14 11:41:19Z

1

I think the Regular expression implementation of substring along with lpad could get you there:

select
  val,
  lpad (coalesce (substring (val, '\d+'), ''), 5, '0') ||
  lpad (coalesce (substring (val, '[A-Za-z]+'), ''), 5, '0') ||
  lpad (coalesce (substring (val, '\++'), ''), 5, '0') as sorting_code
from t

answered Nov 14, 2017 at 11:41

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2017-11-14 11:38:51Z

1

you can do it which such bicycle:

t=# with s(v) as (values('12e+'),('234rr++'),('9999mo'))
, m as (select regexp_matches(v,'[a-z]{1,}','g') l,v,e,p , max(p) over(partition by v)
from s, regexp_split_to_table(v,'[a-z]') with ordinality o (e,p)
)
select lpad(m.e,5,'0')||lpad(m.l[1],5,'0')||lpad(n.e,5,'0') from m join m n on m.v = n.v and m.p=1 and n.p = n.max;
    ?column?
-----------------
 000120000e0000+
 00234000rr000++
 09999000mo00000
(3 rows)

answered Nov 14, 2017 at 11:38

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Create new string from existing one using regex in Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related