1

I'm using the below SQL code in order to split the value of a column into another column but it is not working the way I expected. please find the below code along with the output which I'm getting.

Query:

select 
upper(regexp_replace(regexp_substr(db_attributes,'.([^\.-]+),([^\,-]+), ([^\,-]+)[so]?[\,-]',1,1,'i',1),'[so]$','') ) AS servername, db_attributes
from table_name

Current Output:

servername                  db_attributes
HSBC                        bsprod04.hbtr.adroot.hsbc, 49465, BSPROD04\PTRFRDS01
NULL                        hkw00100308c01.hbap.adroot.hsbc, 2433, HKW00100308C01
NULL                        ora-abacogp.de.hsbc, 1626, ABACOGP

Desired Output:

servername                  db_attributes
BSPROD04\PTRFRDS01          bsprod04.hbtr.adroot.hsbc, 49465, BSPROD04\PTRFRDS01
HKW00100308C01              hkw00100308c01.hbap.adroot.hsbc, 2433, HKW00100308C01
ABACOGP                     ora-abacogp.de.hsbc, 1626, ABACOGP

Regards.

Kalamarico
  • 5,466
  • 22
  • 53
  • 70
Vikas
  • 199
  • 1
  • 7
  • Please give the content of the `table_name` table. – Dominique Nov 13 '17 at 11:01
  • @Dominique table has the column name called 'db_attributes' which has all the server information, so I would like to split the end value of the column 'db_attributes' into a new column called 'servername' and would like to see the output as mentioned above. – Vikas Nov 13 '17 at 11:04
  • Regular expressions are going to be much slower than `SUBSTR` and `INSTR`. – MT0 Nov 13 '17 at 12:18
  • @MT0, in a table with 3 records, it won't make much difference. Besides, let's say that there are 100,000+ records in the table... How much slower is a single `regexp_substr` call going to be (per record), compared to a combination of `substr` and `instr` calls? – Peter Abolins Nov 13 '17 at 12:31
  • @PeterAbolins https://stackoverflow.com/a/41166356/1509264 tests there suggest regular expressions can be 40 times slower than string functions. – MT0 Nov 13 '17 at 12:50
  • @MT0, Thanks... and as per his analysis, anchoring the regular expression properly speeds up the query significantly - even if the end result (according to him) is still slower than using string operations. – Peter Abolins Nov 13 '17 at 13:04

2 Answers2

0

Basically, if the server name is always the last value in the attributes (comma-separated) list, then don't bother looking at the rest of the attributes at all.

SELECT 
      upper(regexp_substr(db_attributes, '[^,]+$',1,1)) AS servername
    , db_attributes
FROM
    tablename

UPDATE

And, if you want to test it, here is an SQLFiddle link.

Peter Abolins
  • 1,520
  • 1
  • 11
  • 18
0

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( db_attributes ) AS
SELECT 'bsprod04.hbtr.adroot.hsbc, 49465, BSPROD04\PTRFRDS01' FROM DUAL UNION ALL
SELECT 'hkw00100308c01.hbap.adroot.hsbc, 2433, HKW00100308C01' FROM DUAL UNION ALL
SELECT 'ora-abacogp.de.hsbc, 1626, ABACOGP' FROM DUAL

Query 1:

SELECT REGEXP_SUBSTR( db_attributes, '\S+$' ) AS servername,
       db_attributes
FROM   table_name

Results:

|         SERVERNAME |                                         DB_ATTRIBUTES |
|--------------------|-------------------------------------------------------|
| BSPROD04\PTRFRDS01 |  bsprod04.hbtr.adroot.hsbc, 49465, BSPROD04\PTRFRDS01 |
|     HKW00100308C01 | hkw00100308c01.hbap.adroot.hsbc, 2433, HKW00100308C01 |
|            ABACOGP |                    ora-abacogp.de.hsbc, 1626, ABACOGP |

Query 2:

SELECT SUBSTR( db_attributes, INSTR( db_attributes, ' ', -1 ) + 1 ) AS servername,
       db_attributes
FROM   table_name

Results:

|         SERVERNAME |                                         DB_ATTRIBUTES |
|--------------------|-------------------------------------------------------|
| BSPROD04\PTRFRDS01 |  bsprod04.hbtr.adroot.hsbc, 49465, BSPROD04\PTRFRDS01 |
|     HKW00100308C01 | hkw00100308c01.hbap.adroot.hsbc, 2433, HKW00100308C01 |
|            ABACOGP |                    ora-abacogp.de.hsbc, 1626, ABACOGP |
MT0
  • 143,790
  • 11
  • 59
  • 117