CODEWITHSUNDEEP
sqlregexpostgresql
soling
soling

Reputation: 551

Create new string from existing one using regex in Postgresql

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

Upvotes: 1

Views: 355

Answers (2)

Hambone
Hambone

Reputation: 16407

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

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51649

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)

Upvotes: 1

Related Questions