SQL 查询以获取所有员工的姓名，他们的姓名中以“A”作为中间字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6781083/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to get all those names of employees,who have 'A' as their middle character in their name
提问by parmanand
E.g:- BRAKE,CRANE etc.
例如：- 制动器、起重机等。
In my employees table , I have ENAME,ENO,JOB,SALARY.
在我的员工表中，我有ENAME,ENO,JOB,SALARY.
Here, I want to extract out those enames that have an 'A' as the center character in their name.
在这里，我想提取那些名称中以“A”作为中心字符的 ename。
If length of ename is odd,then center one, so i need to detect odd and even position in ename.
如果 ename 的长度是奇数，则居中，所以我需要检测 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)
;
回答by Paul
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.
这适用于奇数长度的字符串，我认为这是您想要的。下次请不要使用这样的大写字母。我花了5分钟才读完你的帖子。
SELECT `ENAME` FROM `EMPLOYEES` WHERE SUBSTR(`ENAME`, LENGTH(`ENAME`)/2+1, 1) = 'A'
回答by JNK
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.
第二部分检查中间的 2 个字母是否为偶数长度，以查看其中一个是否为 A。
This is SQL Server syntax but I think Oracle should be similar.
这是 SQL Server 语法，但我认为 Oracle 应该类似。
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%')
回答by Dave Costa
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
回答by Deep Shikha
You may even try this one:
你甚至可以试试这个：
select ename from emp where substr(ename,ceil((length(ename))/2),1)='A';
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.
这适用于偶数和奇数长度的字符串......希望它有所帮助。
回答by Emil Condrea
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)
您使用的是什么数据库服务？（例如在 MS SQL 服务器中，您必须使用 Len）
