Oracle Character Functions: CONCAT, LENGTH, INSTR

This lesson introduces three commonly used Oracle string functions: CONCAT, LENGTH, and INSTR. These functions are essential for manipulating and analyzing string data in SQL queries.

1. CONCAT: Combining Strings

The CONCAT function (or the || operator) combines two or more strings into a single string. Oracle provides two ways to concatenate strings:

• Using || Operator: This is the most common method. It allows you to join multiple strings (columns, expressions, or literals) by chaining the operator. For example, to combine city, state, and zip code with separators:

SELECT CITY || ', ' || STATE || '  ' || ZIP
FROM CUSTOMER;
    

Output:

CITY||','||STATE||''||ZIP
-----------------------------------
Omaha, NE  98765
Makena, HI  99800
Hollywood, CA  10293
Kihei, HI  39873
    

• Using CONCAT(a, b) Function: This function concatenates two strings at a time. To join more than two strings, you must nest multiple CONCAT calls, which is less common due to its complexity.

SELECT CONCAT(CONCAT(CITY, ', '), STATE)
FROM CUSTOMER;
    

Note: The || operator is preferred for its simplicity and flexibility when combining multiple strings.

2. LENGTH: Counting Characters

The LENGTH function returns the number of characters in a string, column, or expression. For VARCHAR2 columns, it counts the actual data length, not the defined column size. If the input is NULL, the function returns NULL.

Example: To find the length of customer names in the CUSTOMER table:

SELECT FIRSTNAME, LENGTH(FIRSTNAME) AS Name_Length
FROM CUSTOMER;
  

Output:

FIRSTNAME  Name_Length
---------- -----------
Sarah      5
Tom        3
Emily      5
NULL       NULL
  

This is useful for data validation or analyzing string lengths in a dataset.

3. INSTR: Locating Substrings

The INSTR function searches for a substring within a string and returns the position of its first character. It is case-sensitive and ideal for tasks like data parsing, cleaning, or extracting specific patterns.

Syntax:

INSTR(string, substring [, start_position [, occurrence]])
  

• string: The source string to search.
• substring: The string to find.
• start_position (optional): Where to start the search (default is 1, the beginning).
• occurrence (optional): Which occurrence of the substring to find (default is 1, the first).

If the substring is not found, INSTR returns 0.

Examples:

1. Basic Search: Find the position of 'WOR' in 'HELLO WORLD':

SELECT INSTR('HELLO WORLD', 'WOR') AS Position
FROM DUAL;
    

Output:

Position
--------
7
    

This shows 'WOR' starts at position 7.

2. Search with Start Position: Find 'FISH' in LOG_TEXT starting from position 20:

SELECT INSTR(UPPER(LOG_TEXT), 'FISH', 20) AS POS, LOG_TEXT
FROM PET_CARE_LOG
WHERE INSTR(UPPER(LOG_TEXT), 'FISH', 20) > 0;
    

This limits results to rows where 'FISH' appears after position 20.

3. Find Second Occurrence: Find the second 'O' in the Author column of the MAGAZINE table:

SELECT Author, INSTR(Author, 'O', 1, 2) AS Second_O
FROM MAGAZINE;
    

Output:

AUTHOR                Second_O
--------------------- --------
BONHOEFFER, DIETRICH  5
CHESTERTON, G.K.      0
RUTH, GEORGE HERMAN   0
WHITEHEAD, ALFRED     0
CROOKES, WILLIAM      4
    

This shows the second 'O' in 'BONHOEFFER' (position 5) and 'CROOKES' (position 4), or 0 if no second 'O' exists.

4. Nested INSTR: Use INSTR to set the starting position dynamically, e.g., after the first period:

SELECT INSTR(LOG_TEXT, 'FISH', INSTR(LOG_TEXT, '.')) AS POS
FROM PET_CARE_LOG;
    



This searches for 'FISH' starting after the first period in LOG_TEXT.

Comparison with LIKE: Unlike the LIKE operator, which is limited to WHERE or HAVING clauses and supports complex pattern matching, INSTR can be used in most parts of a query (except the FROM clause) and is better for precise position-based searches.

Practical Applications

These functions are powerful for data manipulation:

• CONCAT (or ||): Combine fields for display, e.g., creating full addresses.
• LENGTH: Validate data (e.g., ensure fields meet length requirements).
• INSTR: Parse strings, locate patterns, or extract segments in data cleaning or log analysis.

Exercise

Practice using CONCAT, LENGTH, and INSTR with the CUSTOMER or MAGAZINE tables to combine fields, measure string lengths, and locate substrings.

Next Steps

The next lesson will explore additional Oracle string functions to further enhance your data manipulation skills.