Oracle / PLSQL: Parse a string value and then return a substring

Question: I'm trying to parse a value in an Oracle field. I have to find the occurrence of the 3rd and 4th underscore in a string and then return the value that resides between these two underscores.

For example, the field may contain the following value:

'F:\Siebfile\YD\S_SR_ATT_1-60SS_1-AM3L.SAF'

In this case, I need to return the value of '1-60SS', as this is the value that resides between the 3rd and 4th underscores.

Answer: This is not a simple task. Therefore, we will need to create a function in Oracle that parses the string for us.

The function below uses the INSTR function to determine the positions of the 3rd and 4th underscore. Then it uses the SUBSTR function to return the value between the two underscores.

create or replace function parse_value (pValue varchar2)
   return varchar2
is
   v_pos3 number;
   v_pos4 number;

begin

   /* Return 3rd occurrence of '_' */
   v_pos3 := INSTR (pValue, '_', 1, 3) + 1;

   /* Return 4rd occurrence of '_' */
   v_pos4 := INSTR (pValue, '_', 1, 4);

   return SUBSTR (pValue, v_pos3, v_pos4 - v_pos3);

end parse_value;

Learn more about the INSTR function.

Learn more about the SUBSTR function.

Oracle / PLSQL: Extract arithmetic operators from a string

 Oracle / PLSQL: Extract arithmetic operators from a string

Question: I'd like to know how to handle this issue. I have a stored procedure that has an input variable:

v_in varchar2(200);

The value will be something like this:

1+2*30+40

I need to extract only the arithmetic operators (+, *, and +) so that I can use them in a query. How can I do this?

Answer: To extract the arithmetic operators from your varchar2 variable, you will need to use both the translate and REPLACE functions.

For example,

TRANSLATE('1+2*30+40','01234',' ')
Result: '+* +'

REPLACE('+* +', ' ')
Result: '+*+'

So you could combine these functions into:

REPLACE(TRANSLATE('1+2*30+40','01234',' '), ' ')
Result: '+*+'

Learn more about the TRANSLATE function.

Learn more about the REPLACE function.

For more tutorials visit our Oracle PL/SQL Blog.

 

Oracle / PLSQL: Sort a varchar2 field as a numeric field

Question: I have a field defined in Oracle as a varchar2, but it contains numbers. When I use an "order by" clause, the records are sorted ascending by character. But I want to sort it numerically without changing the datatype from varchar2 to numeric. Is there any solution?

Answer: To sort your field numerically, there are two possible solutions:

Solution #1

This first solution only works if the varchar2 field contains all numbers. To do this, you will need to add another field to your "order by" clause that evaluates to a number, but you don't have to include this new field in your SELECT portion of the SQL.

For example,

You may have a supplier table defined as:

create table supplier
( supplier_id varchar2(10) not null,
  supplier_name varchar2(60)
);

When you perform the following select statement, the supplier_id field sorts alphabetically, even though it contains numbers.

select * from supplier
order by supplier_id;

However, you could execute the following SQL, which will return a numerically sorted supplier_id:

select * from supplier
order by TO_NUMBER(supplier_id);

This SQL converts the supplier_id field to a numeric value and then sorts the value in ascending order. This solution returns an error if not all of the values in the supplier_id field are numeric.

Solution #2 (more eloquent solution)

We'd like to thank Kamil for suggesting this solution.

This solution will work even if the varchar2 field contains non-numeric values.

Again, we'll demonstrate this solution on the supplier table, defined as:

create table supplier
( supplier_id varchar2(10) not null,
  supplier_name varchar2(60)
);

Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.

For example:

select * from supplier
order by LPAD(supplier_id, 10);

This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.

Please note that if your numbers in the supplier_id field are longer than 10 digits, you may need to increase the second parameter on the LPAD function.

Learn more about the LPAD function.

 

Oracle / PLSQL: Test a string for an alphanumeric value

Question: In Oracle, I want to know if a string value contains alphanumeric characters only. How can I do this?

Answer: To test a string for alphanumeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))

string1

The string value that you are testing.

This function will return a null value if string1 is alphanumeric. It will return a value "greater than 0" if string1 contains any non-alphanumeric characters.

For example,

LENGTH(TRIM(TRANSLATE('Tech on the Net!!!', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))
Result: 3

LENGTH(TRIM(TRANSLATE('Tech1Net2^', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))
Result: 1

LENGTH(TRIM(TRANSLATE('Tech on the Net567', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))
Result: null

Learn more about the TRANSLATE function.

Learn more about the LENGTH function.

Learn more about the TRIM function.

 

Oracle / PLSQL: Test a string for an alphabetic value

Question: In Oracle, I want to know if a string value contains alphabetic characters only. How can I do this?

Answer: To test a string for alphabetic characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))

string1

The string value that you are testing

This function will return a null value if string1 is alphabetic. It will return a value "greater than 0" if string1 contains any non-alphabetic characters.

For example,

LENGTH(TRIM(TRANSLATE('Tech3', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))
Result: 1

LENGTH(TRIM(TRANSLATE('Tech1Net2', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))
Result: 2

LENGTH(TRIM(TRANSLATE('Tech on the Net', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))
Result: null

Learn more about the TRANSLATE function.

Learn more about the LENGTH function.

Learn more about the TRIM function.

 

Oracle / PLSQL: Test a string for a numeric value

Question: In Oracle, I want to know if a string value is numeric only. How can I do this?

Answer: To test a string for numeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

string1

The string value that you are testing.

This solution uses the TRANSLATE, LENGTH, and TRIM functions to test a string for a numeric value if the numeric value is properly formatted. It will return a null value if string1 is numeric. It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example,

SELECT LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))) FROM dual;
Result: 1

SELECT LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))) FROM dual;
Result: 2

SELECT LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))) FROM dual;
Result: null

SELECT LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' '))) FROM dual;
Result: null

Although this first method will test a string if it is a properly formatted number, it would allow a number to pass such as -+1..8++-, which is of course not a number. A better way to test a string for a numeric value is to create your own function that uses the TO_NUMBER function to test the string value.

For example:

CREATE FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END is_number;

This new function called is_number would return 1 if the value is numeric and 0 if the value is NOT numeric. You could execute the is_number function as follows:

SELECT is_number('123') FROM dual;
Result: 1

SELECT is_number('123b') FROM dual;
Result: 0

SELECT is_number('-+1..8++-') FROM dual;
Result: 0

So this method would catch the fact that -+1..8++- is not a valid number.

 

Oracle / PLSQL: Dealing with apostrophes/single quotes in strings

Question: How can I handle apostrophes and single quotes in strings? As you know, single quotes start and terminate strings in SQL.

Answer: Now it is first important to remember that in Oracle, you enclose strings in single quotes. The first quote denotes the beginning of the string and the second quote denotes the termination of the string.

If you need to deal with apostrophes/single quotes in strings, your solution depends on where the quote is located in the string.

We'll take a look at 4 scenarios where you might want to place an apostrophe or single quote in a string.

Apostrophe/single quote at start of string

When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT '''Hi There'
FROM dual;

would return

'Hi There

Apostrophe/single quote in the middle of a string

When the apostrophe/single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:

SELECT 'He''s always the first to arrive'
FROM dual;

would return

He's always the first to arrive

Apostrophe/single quote at the end of a string

When the apostrophe/single quote is at the end of a string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Smiths'''
FROM dual;

would return

Smiths'

Apostrophe/single quote in a concatenated string

If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes for Oracle to display a quote symbol. For example:

SELECT 'There' || '''' || 's Henry'
FROM dual;

would return

There's Henry