Oracle SUBSTR Function

Your rating: None Average: 5 (3 votes)

The SUBSTR function is used to extract characters from a word or sentence.

For Example, I have run into many situations were I need to apply different programming logic based on characters in a string.

Suppose I have an employee table with an employee_id and employee_name (First, Last, middle) and I want to store the middle name in my database as middle initial only. So I only need the first character of the middle name.

I can simply do this by using a SUBSTR on the middle name field.

SELECT SUBSTR(middle_name,0,1) middle_initial FROM <employee>

The first argument in the example above for the SUBSTR function is the column name. The second argument is a number which is the starting position of where you want to grab the character from; In this example I want the first character so I use 0 or you can use 1). The last argument is how many characters you want to grab from the string, in this case I want only 1 character so I said 1.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Lepa's picture
User offline. Last seen 3 weeks 3 days ago. Offline
Joined: 06/23/2008
Posts: 590
Re: Oracle SUBSTR Function

Zooz, you might want to check this post. It is the REGEXP_SUBSTR available in Oracle 10g and up. It is much more powerful than the normal SUBSTR.

Cheers!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Guest's picture
Guest (not verified)
Re: Oracle SUBSTR Function

Hi ,
I need to ignore the first character if it s alphabet by using oracle.can we use substr for this?
Record sample is below.
A383493
D29393
j93303
n93430
I need to fetch the records as
383493
29393
j93303
93430

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
8 + 8 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.