Oracle / PLSQL: Difference between an empty string and a null value

Question: What is the difference between an "empty" value and a "null" value? When I select those fields that are "empty" versus "null", I get two different result sets.

Answer: An empty string is treated as a null value in Oracle. Let's demonstrate.

We've created a table called suppliers with the following table definition:

create table suppliers
( supplier_id number,
  supplier_name varchar2(100)
);

Next, we'll insert two records into this table.

insert into suppliers (supplier_id, supplier_name )
values ( 10565, null );

insert into suppliers (supplier_id, supplier_name )
values ( 10567, '' );

The first statement inserts a record with a supplier_name that is null, while the second statement inserts a record with an empty string as a supplier_name.

Now, let's retrieve all rows with a supplier_name that is an empty string value as follows:

select * from suppliers
where supplier_name = '';

When you run this statement, you'd expect to retrieve the row that you inserted above. But instead, this statement will not retrieve any records at all.

Now, try retrieving all records where the supplier_name contains a null value:

select * from suppliers
where supplier_name is null;

When you run this statement, you will retrieve both rows. This is because Oracle has now changed its rules so that empty strings behave as null values.

It is also important to note that the null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value. Instead, you must use the IS NULL and IS NOT NULL conditions.

 

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

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.

 

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