String extraction in postgres

Question

There is this value in a column, below i have shown i used to extract the data from that field.

with A4 as 
(
select 'govinda j/INDIA_MH/9975215025' as employee_name from dual
)
select employee_name , 
TRIM(SUBSTR(upper(A4.employee_name),1,INSTR(A4.employee_name,'/',1,1)-1)) AS employee_name1,
  TRIM(SUBSTR(upper(A4.employee_name),INSTR(A4.employee_name,'/',1,1)+1,INSTR(A4.employee_name,'_',1,1)-INSTR(A4.employee_name,'/',1,1)-1)) AS Country,
  TRIM(SUBSTR(upper(A4.employee_name),INSTR(A4.employee_name,'_',1,1)+1,INSTR(A4.employee_name,'/',1,2)-INSTR(A4.employee_name,'_',1,1)-1)) AS State

   from A4

Output

EMPLOYEE_NAME                   EMPLOYEE_NAME1   COUNTRY       STATE
govinda j/INDIA_MH/9975215025   GOVINDA J        INDIA         MH

Now our database system is changed, I am struggling to achieve same in postgres.

Are you really still using Postgres 9.1? That has been out of support for over 2 years now. You should really plan the upgrade to a supported version (e.g. 10) as soon as possible. — user330315
– user330315, Commented Sep 20, 2018 at 11:17

Gordon Linoff · Accepted Answer · 2018-09-20 11:16:51Z

1

One option is split_part():

select split_part(employee_name, '/', 1) as employee_name1,
       split_part(split_part(employee_name, '/', 2), '_', 1) as country,
       split_part(split_part(employee_name, '/', 2), '_', 2) as state
from a4

answered Sep 20, 2018 at 11:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Mayur Randive Over a year ago

There is another column validate which is as below

Mayur Randive Over a year ago

Is there any similar function like REGEXP_COUNT in Postgres

Gordon Linoff Over a year ago

@MayurRandive . . . New questions should be asked as new questions. This question is specifically about one format. Continually modifying a question is not a good practice.

Collectives™ on Stack Overflow

String extraction in postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related