CODEWITHSUNDEEP
sqloracle-databasesubstr
parmanand
parmanand

Reputation: 478

Query to get all those names of employees,who have 'A' as their middle character in their name

E.g:- BRAKE,CRANE etc.

In my employees table , I have ENAME,ENO,JOB,SALARY.

Here, I want to extract out those enames that have an 'A' as the center character in their name.

If length of ename is odd,then center one, so i need to detect odd and even position in ename.

So, I tried this, but stuck up ,so can i expect a help from here?

SELECT ENAME
FROM EMPLOYEES
WHERE A IN
(SELECT ENAME,
       SUBSTR(ENAME,LENGTH(ENAME)/2+1,1)
FROM EMPLOYEES)
;

Upvotes: 1

Views: 34578

Answers (6)

sumanth bs
sumanth bs

Reputation: 1

SELECT ename FROM employees
WHERE instr(ename, 'A', 1, 1) = round(length(ename) / 2);

Upvotes: 0

Deep Shikha
Deep Shikha

Reputation: 1

You may even try this one:

select ename from emp where substr(ename,ceil((length(ename))/2),1)='A';

This will work for both even and odd length strings...hope it helped.

Upvotes: 0

JNK
JNK

Reputation: 65177

This checks first that they have an odd number of letters in the name, then does the check.

The second part checks the middle 2 letters for even-numbered lengths to see if either is A.

This is SQL Server syntax but I think Oracle should be similar.

SELECT ENAME
FROM EMPLOYEES
WHERE ((LEN(ENAME) %2) = 1
AND SUBSTRING(ENAME, LEN(Ename)/2+1, 1) = 'A')
OR
((LEN(ENAME) %2) = 0
AND SUBSTRING(ENAME, LEN(ENAME)/2-1, 2) LIKE '%A%')

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48121

SELECT ename
  FROM employees
WHERE
INSTR(
CASE
  WHEN MOD(LENGTH(ename),2) = 0 THEN SUBSTR( ename, LENGTH(ename)/2, 2 )
  ELSE SUBSTR( ename, (1+LENGTH(ename)/2), 1 )
END,
'A'
) > 0

Upvotes: 1

Emil Condrea
Emil Condrea

Reputation: 9973

I think this is what you mean:

SELECT ENAME FROM EMPLOYEES where ENAME=SUBSTR(ENAME,LENGTH((ENAME+1)/2),1)

What Database service are you using? (for instance in MS SQL server you must use Len)

Upvotes: 0

Paul
Paul

Reputation: 141837

This works for odd length strings, which I think is what you wanted. Next time please don't use caps like that. It took me 5 minutes just to read your post.

SELECT `ENAME` FROM  `EMPLOYEES` WHERE SUBSTR(`ENAME`, LENGTH(`ENAME`)/2+1, 1) =  'A'

Upvotes: 3

Related Questions