CODEWITHSUNDEEP
sqloracle-databasesubstr
fnr
fnr

Reputation: 9667

Oracle SQL - How to Cut out characters from a string with SUBSTR?

I have values like "ABC1234", "ABC", "DEF456", "GHI" etc. in a specific column which I need.

Now I need to split this string but only if the character (e.g. "ABC") are followed by digits.

So if the value is "ABC1234" then I need to cut out ABC and 1234 seperated. But if there is only "ABC" as a value, I just need the "ABC". I can't find any solution with SUBSTR. Do you have any idea?

Note: The length of the characters can differ from 1 to 10 and also the length from the digits (sometimes there isn't any like I showed you).

Upvotes: 1

Views: 6673

Answers (4)

Littlefoot
Littlefoot

Reputation: 142705

Would something like this do?

SQL> with test (col) as
  2    (select '"ABC1234", "ABC", "dEf456", "123GHI", "456"' from dual),
  3  inter as
  4    (select trim(regexp_substr(replace(col, '"', ''), '[^,]+', 1, level)) token
  5     from test
  6     connect by level <= regexp_count(col, ',') + 1
  7    )
  8  select regexp_substr(token, '^[a-zA-Z]+') letters,
  9         regexp_substr(token, '[0-9]+$') digits
 10  from inter
 11  where regexp_like(token, '^[a-zA-Z]+[0-9]+$');

LETTERS    DIGITS
---------- ----------
ABC        1234
dEf        456

SQL>

Upvotes: 1

kanagaraj
kanagaraj

Reputation: 442

Try this below query for scenarios mentioned , I didn't split if characters followed by numbers:

with test (col) as
  (select 'ABC1234' from dual union all
   select 'ABC'     from dual union all
   select 'dEfH456'  from dual union all
   select '123GHI'  from dual union all
   select '456'     from dual
  )

  select col,reverse(trim(regexp_replace(reverse(col),'^[0-9]+',' '))) string ,trim(regexp_replace(col,'^[a-zA-Z]+',' ')) numbers from test

if like to move that characters&string to any place my case statement

with test (col) as
  (select 'ABC1234' from dual union all
   select 'ABC'     from dual union all
   select 'dEfH456'  from dual union all
   select '123GHI'  from dual union all
   select '456'     from dual
  )

  select v.col,case when v.string=v.numbers THEN NULL ELSE string end string , v.numbers
  from (select col,reverse(trim(regexp_replace(reverse(col),'^[0-9]+',' '))) string ,trim(regexp_replace(col,'^[a-zA-Z]+',' ')) numbers from test) v

Upvotes: 1

XING
XING

Reputation: 9886

So if the value is "ABC1234" then I need to cut out ABC and 1234 seperated. But if there is only "ABC" as a value, I just need the "ABC".

Amidst of other solutions, I propose one solution as shown below:

Logic:

1) Replace all the digits to 1. Check the position of the digit occurring in the string. If there is no digit in the string then use the String.

2) Extract the alphabets from 1st position to the position where digit starts.

3) Extract the digit from the position it starts till end. If digit doesnot exists the set it NULL

  --Dataset Preparation
    with test (col) as
      (select 'ABC1234' from dual union all
       select 'ABC'     from dual union all
       select 'dEfH456'  from dual union all
       select '123GHI'  from dual union all
       select '456'     from dual
      )
     --Query
    select col Original_Column, 
           CASE 
              WHEN (instr(regexp_replace(col,'[0-9]','1'),'1',1)) = 0
           then col
           else
           substr( col,1,instr(regexp_replace(col,'[0-9]','1'),'1',1)-1) 
           end Col_Alp,

           CASE 
              WHEN (instr(regexp_replace(col,'[0-9]','1'),'1',1)) = 0
            then NULL
            Else
            substr( col,instr(regexp_replace(col,'[0-9]','1'),'1',1)) 
            END col_digit    
   from test
   where regexp_like(col, '^[a-zA-Z0-9]+$');

Result:

SQL> /
Original_Column Col_Alp col_digit
----------      -----   -----
ABC1234      ABC       1234
ABC          ABC       NULL
dEfH456      dEfH      456
123GHI       NULL       123GHI
456          NULL       456

Upvotes: 1

MT0
MT0

Reputation: 167962

Using SUBSTR (and INSTR and TRANSLATE):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE data ( value ) AS
SELECT 'ABC1234'     FROM DUAL UNION ALL
SELECT 'ABC123D'     FROM DUAL UNION ALL
SELECT 'ABC '        FROM DUAL UNION ALL
SELECT 'ABC'         FROM DUAL UNION ALL
SELECT 'DEFG456'     FROM DUAL UNION ALL
SELECT 'GHI'         FROM DUAL UNION ALL
SELECT 'JKLMNOPQRS9' FROM DUAL;

Query 1:

SELECT value,
       SUBSTR( value, 1, first_digit - 1 ) AS prefix,
       TO_NUMBER( SUBSTR( value, first_digit ) ) AS suffix
FROM   (
  SELECT value,
         INSTR(
           TRANSLATE( value, '-1234567890', ' ----------' ),
           '-',
           1
         ) AS first_digit
  FROM   data
)
WHERE  SUBSTR( value, first_digit ) IS NOT NULL
AND    TRANSLATE( SUBSTR( value, first_digit ), '-1234567890', ' ' ) IS NULL

Results:

|       VALUE |     PREFIX | SUFFIX |
|-------------|------------|--------|
|     ABC1234 |        ABC |   1234 |
|     DEFG456 |       DEFG |    456 |
| JKLMNOPQRS9 | JKLMNOPQRS |      9 |

Upvotes: 1

Related Questions