Written by Sai Sandeep Thota on March 18, 2013 in How to > Programming > SQL

How to Get MIDDLE Character of a String in SQL

GET ALERTS:

Get our Latest updates delivered to your mailbox!

In this article we will discuss about how to get middle character of a string in SQL. We have already explained about Substring function in our earlier article. We have a different case here, We need to get Middle character of a String. Only Middle Character? What if we need 3 characters after the middle character? So inorder to achieve this we first need to calculate the length of the string and we need to do some extra things. We have discussed the same below. We have written a basic Query and an example Query for better understanding.

Get Middle Character of a String

We have a function called substr() in Oracle.

In Oracle the Command will look something like:

sql > SELECT FIELD, SUBSTR(FIELD, LENGTH(FIELD)/2+1,1) FROM tblname;

For example we’ll take the Employees table and get the Middle Letter from the Names of Employees. So let us take a look at how the query looks like:

sql > SELECT ename, SUBSTR(ename, LENGTH(ename)/2+1,1) FROM Emp;

So what is this LENGTH(ename)/2+1? LENGTH() is used to find the length of a string, So we are using the length() function to find the length and we are dividing it with 2 and adding 1 (5/2 gives 2.5, If it is rounded it takes 2, So we add 1) inorder to get the middle position. The next 1 is used to fetch n number of letters. So if you want to fetch 2 letters after the middle position we’ll use length(ename)/2,2.

MySQL

In MySQL the function will be slightly changing. Instead of SUBSTR() function we have MID() function here. The query looks like:

mysql > SELECT field, MID(field, LENGTH(field)/2,1) FROM tblname;

If we consider the same employees table as above, the Query is:

mysql > SELECT ename, MID(ename, LENGTH(ename)/2,1) FROM Emp;

If you got any better ideas, Let us know! 🙂

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: