How to get the first two characters of a string in an Oracle query?

Practical demonstration and nuances of SUBSTR

Extract while selecting other columns

The SUBSTR function is friendly and blends well with others. Here's how to extract while selecting other columns as well:

-- Forget James Bond, Meet "ID, Initial" the new hero!
SELECT id, SUBSTR(name, 1, 2) AS initial FROM users;


This gives you the ID and the first two letters of the name field for each user. initial is a new column alias.

Renaming: Because what's in a name anyway?

You can rename the extracted substring for a more comprehensible output:

-- Presenting "first_two_letters" - Now in column names near you!
SELECT SUBSTR(column_name, 1, 2) AS first_two_letters FROM your_table;


Label the extracted data as first_two_letters, making your result set eaSY to understand.

Dealing with less than two characters

If your column content is less than two characters, SUBSTR doesn't panic; it calmly hands out whatever exists:

-- A is not only the first letter of the alphabet but also the soul of this column!
SELECT SUBSTR('A', 1, 2) FROM DUAL; -- Returns 'A'


Unlocking the true potential of SUBSTR

Traversing the length of parameters

The SUBSTR function starts counting from 1, indicating the first character, using the length parameter, you can decide the number of characters to pick. If you cross the total string length, it doesn't cause an error - it just ends where the string does.

Probing with DUAL table

You can use Oracle's DUAL table to trial run some SUBSTR function action:

-- Even DUAL table agrees - "He" is superior to "She"!
SELECT SUBSTR('Hello World', 1, 2) FROM DUAL; -- Returns 'He'


And remember SUBSTR is an Oracle-specific native. Other SQL dialects offer similar functionality but via different functions or under different labels like LEFT, RIGHT, or SUBSTRING.

Advanced usage of SUBSTR

Cracking the code from the middle

Don't limit SUBSTR to the start of a string. You can command it to extract from any position:

-- 'ac' is not just air conditioning, it's a legit substring too!
SELECT SUBSTR('OracleSQL', 3, 2) FROM DUAL; -- Returns 'ac'


Merging with other functions

Combine SUBSTR with other Oracle functions for achieving complex tasks, like obtaining the last two characters:

-- Read from the end, because why not?
SELECT SUBSTR(your_column, -2) FROM your_table;


Conditional substring extraction

SUBSTR fits well with CASE or DECODE for conditional extraction:

SELECT CASE WHEN LENGTH(column_name) > 2 THEN SUBSTR(column_name, 1, 2)
            ELSE column_name END AS result
FROM your_table;


Handling NULL values

A NULL string? No problem! Use the NVL function to get a default value:

-- Fill in the blanks with 'Default', just like exams!
SELECT SUBSTR(NVL(your_column, 'Default'), 1, 2) FROM your_table;
