Oracle PL SQL STRING Functions

Question

I have a column which has values or rows as combination of name & Code as seen below.

Data_Current

Column_X
---------------------------
Saint_Peter_King_Jr_0001
Kishore_Prasad_Misra_1253
George_Floyd_21543
Veer_Venkata_Sai_Narsimha_Kishore_00015

and I want to get Name & Code in different column but the separator '_' and its occurrence(Count & Position) is not same in every row or value as seen above.

So how to read the string from the right and extract the name & Code as below

Result Required

Name                                    Code
---------------------------------------------
Saint_Peter_King_Jr                      0001
Kishore_Prasad_Misra                     1253
George_Floyd                            21543
Veer_Venkata_Sai_Narsimha_Kishore       00015

I tried but instr function but INSTR( string, substring [, start_position [, th_appearance ] ] ) but the appearance is not constant and changes based on name. How to make it dynamic?

Of course the difficulty would be completely eliminated if you were to fix your seriously flawed data model. "name" and "code" should be two separate columns. Your current design violates standard Third Normal Form design, which is the heart of relational data design. — EdStevens
– EdStevens, Commented Jun 19, 2020 at 19:08

Abra · Accepted Answer · 2020-06-19 18:53:57Z

1

Below SQL uses Oracle built-in functions INSTR and SUBSTR and assumes that the "code" is always the last part of the string and is always preceded by a single, underscore character, i.e. _

select substr('Veer_Venkata_Sai_Narsimha_Kishore_00015',1,instr('Veer_Venkata_Sai_Narsimha_Kishore_00015','_',-1) - 1) as NAME
      ,substr('Veer_Venkata_Sai_Narsimha_Kishore_00015',instr('Veer_Venkata_Sai_Narsimha_Kishore_00015','_',-1) + 1) as CODE
  from dual

Result is...

NAME                              CODE
--------------------------------- -----
Veer_Venkata_Sai_Narsimha_Kishore 00015

Note that you may substitute the column name, i.e. Column_X for the string literal.

answered Jun 19, 2020 at 18:53

Abra

21k14 gold badges101 silver badges74 bronze badges

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

1 Comment

kiss.shoremishra Over a year ago

Hi Abra, Wonderful and thanks a ton. It worked. -1 after the separator worked perfectly. without -1 it is reading from left but with -1 it is reading from right. great.

Gary_W · Accepted Answer · 2020-06-19 19:19:28Z

1

The regexp_substr method. The with statement just sets up the data, the meat is in the select. The regular expressions handle what you mean by dynamic, i.e. an unknown number of characters of code and/or an unknown number of numbers in the code. The parenthesis in the regular expression define a captured group, which is returned. For the name, the set of any characters before the last underscore followed by 1 or more digits, and for the code, the set of 1 or more digits after any number of any characters and the last underscore.

WITH tbl(column_X) AS (
  SELECT 'Saint_Peter_King_Jr_0001' FROM dual UNION ALL
  SELECT 'Kishore_Prasad_Misra_1253' FROM dual UNION ALL
  SELECT 'George_Floyd_21543' FROM dual UNION ALL
  SELECT 'Veer_Venkata_Sai_Narsimha_Kishore_00015' FROM dual
)
SELECT 
  REGEXP_SUBSTR(column_X, '(.*?)_\d+', 1, 1, NULL, 1) AS NAME,
  REGEXP_SUBSTR(column_X, '.*?_(\d+)', 1, 1, NULL, 1) AS code
FROM tbl;

NAME                                    CODE                                   
--------------------------------------- ---------------------------------------
Saint_Peter_King_Jr                     0001                                   
Kishore_Prasad_Misra                    1253                                   
George_Floyd                            21543                                  
Veer_Venkata_Sai_Narsimha_Kishore       00015                                  

4 rows selected.

edited Jun 19, 2020 at 19:19

answered Jun 19, 2020 at 19:04

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

2 Comments

kiss.shoremishra Over a year ago

Hi Gary, There are numerous records and not just 4, its too difficult to add select query for all the records.

Gary_W Over a year ago

@kiss.shoremishra Please note the WITH is called a CTE for Common Table Expression and here is used to create a data set for the example. The select below the CTE is applied to that data set for the example. You would only use the select below and the FROM clause would be your actual table.

Brian Hoffman · Accepted Answer · 2020-06-19 19:04:14Z

-1

You can't do that with built in functions. You are going to have to write your own function to tackle it procedurally.

answered Jun 19, 2020 at 19:04

Brian Hoffman

243 bronze badges

Collectives™ on Stack Overflow

Oracle PL SQL STRING Functions

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related