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

oracle sql substring between two characters
oracle substring before character
instr and substr in oracle with example
how to get last 4 characters in a string in oracle
oracle sql substring after character
oracle remove last 3 characters from string
oracle sql instr
oracle string functions

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).

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

Oracle TRIM function, Database SQL Reference. Contents The SUBSTR functions return a portion of char , beginning at character position , substring_length If position is positive, then Oracle Database counts from the beginning of char to find the first character. If position is positive, then Oracle Database counts from the beginning of char to find the first character. If position is negative, then Oracle counts backward from the end of char. If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.

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 |

Remove the Last Character from any String in Oracle SQL, 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. In Oracle, SUBSTR function returns the substring from a string starting from the specified position and having the specified length (or until the end of the string, by default). In SQL Server, you can use SUBSTRING function, but it does not allow you to specify a negative start position, and the substring length must be specified .

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

The SUBSTR() function accepts three arguments: str. str is the string that you want to extract the substring. The data type of str can be CHAR , VARCHAR2  The functions Oracle SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4 are to cut out a piece of text from the input string and return it. The returned text length will depend on your parameters that you gave. The SUBSTR oracle syntax is: SUBSTR(<the input text>,<the starting position>[,<the returning string length>])

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>

. If trim_character or trim_source is a character literal, then it is necessary to enclose it in single quotation marks. When no trim_character is specified, then the default value is a blank space. 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.

How do you remove the first and last character of a string in Oracle? The Oracle SUBSTR function is used to get a smaller string (the substring) from within a larger string. Let’s take a look at how you can use it and some examples. Purpose of the Oracle SUBSTR Function. This Oracle SUBSTR function allows you to extract a smaller string from within a larger string.

In Oracle, SUBSTR function returns the substring from a string starting from the Get first 3 characters SELECT SUBSTR('New York', 1, 3) FROM dual; # New  A. Using SUBSTRING with a character string. The following example shows how to return only a part of a character string. From the sys.databases table, this query returns the system database names in the first column, the first letter of the database in the second column, and the third and fourth characters in the final column.

This Oracle tutorial explains how to use the Oracle / PLSQL SUBSTR function with syntax and examples. The Oracle / PLSQL SUBSTR functions allows you to extract a substring from a string. The first position in the string is always 1. length: Optional. It is the number of characters to extract. If this parameter is omitted, the  SUBSTR functions. The SUBSTR functions (SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4) return a portion of string, beginning at a specified position in the string. The functions vary in how they calculate the length of the substring to return. SUBSTR calculates lengths using characters as defined by the input character set.

Comments
  • What about if you have AB12CD34 ?
  • This is not possible
  • Please edit the original post to show an example of actual data before and after processing. Do you have the power to fix the database design or are you stuck with this violation of 1st normal form?
  • This one worked for me. Thank you!
  • This does not work. How I've said ABC123 is not a column, it's a value from over 1.000 different values. Also it can be ABCDEFG1234567 so your SUBSTR with "1, 3" would not work
  • @fnr Updated for different length values. Just use any of the many, many ways to split a delimited string so that the values are in a column. (And then update your data structures so that they are sensibly stored in a column rather than trying to use delimited strings.)
  • I have more than 1.000 values in that column. They are also added dozens daily. I can't write a SELECT for each of them.
  • Ah! I didn't realize that all those columns are in a same column. I edited my answer; have a look, please, @fnr.