Query to get all those names of employees,who have 'A' as their middle character in their name
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'
Display all the employees whose name contain 5 characters length in ORACLE
Use REGEXP_COUNT to count only letters
SELECT ENAME FROM EMP where regexp_count(ENAME, '[A-Z]') = 5 ;
REGEXP_COUNT complements the functionality of the REGEXP_INSTR function by returning the number of times a pattern occurs in a source string. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the number of occurrences of pattern. If no match is found, then the function returns 0.
Oracle, search across multiple optional fields (first, middle, last names)
with names(first_name,middle_name,last_name)
as(select 'John','','Doe' from dual
union
select 'Jane','Mary','Doe' from dual
union
select 'John','','Smith' from dual
union
select 'George','Smith','Michael' from dual
union
select 'John','Lee','Smith' from dual
)
select *
from names
where lower(first_name||middle_name||last_name) like '%john%smith%'
FIRST_ MIDDL LAST_NA
------ ----- -------
John Lee Smith
John Smith
sql query : show name with all vowels
Try using and name LIKE '%a%'
for your vowels, this will search the entire string and not just the last letter.
For example:
SELECT name
FROM world
WHERE name LIKE '%u%'
and name LIKE '%a%'
and name LIKE '%o%'
and name LIKE '%i%'
and name LIKE '%e%'
and name NOT LIKE '% %'
Calling a value starting with $ character in Oracle
If you want to select the names, but with special characters $ and # removed, you can use the TRANSLATE function. Add more characters to the list if you need to.
select translate(name, 'A$#', 'A') from employee;
The function will "translate" the character 'A' to itself, '$' and '#' to nothing (simply removing them from the string), and it will leave all other characters - other than A, $ and # - unchanged. It may seem odd that you need the 'A' in this whole business; and you really don't need 'A' specifically, but you do need some character that you want to keep. The reason for that is Oracle's idiotic handling of null
; don't worry about the reason, just remember the technique.
You may need to remove characters but you don't know in advance what they will be. That can be done too, but you need to be careful not to remove legitimate characters, like the dot (A. C. Green), dash (John Connor-Smith), apostrophe (Betty O'Rourke) etc. You can then do it either with regular expressions (easy to write, but not the most efficient) or with TRANSLATE as above (it looks uglier, but it will run faster). Something like this:
select regexp_replace(name, [^[:alpha:].'-]) from employee
This will replace any character that is not "alpha" (letters) or one of the characters specifically enumerated (dot, apostrophe, dash) with nothing, effectively removing them. Note that dash has a special meaning in character classes, so it must be the last one in the enumeration.
If you need to make the changes in the table itself, you can use an update
statement, using TRANSLATE or REGEXP_REPLACE as shown above.
SQL: parse the first, middle and last name from a fullname field
Here is a self-contained example, with easily manipulated test data.
With this example, if you have a name with more than three parts, then all the "extra" stuff will get put in the LAST_NAME field. An exception is made for specific strings that are identified as "titles", such as "DR", "MRS", and "MR".
If the middle name is missing, then you just get FIRST_NAME and LAST_NAME (MIDDLE_NAME will be NULL).
You could smash it into a giant nested blob of SUBSTRINGs, but readability is hard enough as it is when you do this in SQL.
Edit-- Handle the following special cases:
1 - The NAME field is NULL
2 - The NAME field contains leading / trailing spaces
3 - The NAME field has > 1 consecutive space within the name
4 - The NAME field contains ONLY the first name
5 - Include the original full name in the final output as a separate column, for readability
6 - Handle a specific list of prefixes as a separate "title" column
SELECT
FIRST_NAME.ORIGINAL_INPUT_DATA
,FIRST_NAME.TITLE
,FIRST_NAME.FIRST_NAME
,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
THEN NULL --no more spaces? assume rest is the last name
ELSE SUBSTRING(
FIRST_NAME.REST_OF_NAME
,1
,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
)
END AS MIDDLE_NAME
,SUBSTRING(
FIRST_NAME.REST_OF_NAME
,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
,LEN(FIRST_NAME.REST_OF_NAME)
) AS LAST_NAME
FROM
(
SELECT
TITLE.TITLE
,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
THEN TITLE.REST_OF_NAME --No space? return the whole thing
ELSE SUBSTRING(
TITLE.REST_OF_NAME
,1
,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
)
END AS FIRST_NAME
,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
THEN NULL --no spaces @ all? then 1st name is all we have
ELSE SUBSTRING(
TITLE.REST_OF_NAME
,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
,LEN(TITLE.REST_OF_NAME)
)
END AS REST_OF_NAME
,TITLE.ORIGINAL_INPUT_DATA
FROM
(
SELECT
--if the first three characters are in this list,
--then pull it as a "title". otherwise return NULL for title.
CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
ELSE NULL
END AS TITLE
--if you change the list, don't forget to change it here, too.
--so much for the DRY prinicple...
,CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
END AS REST_OF_NAME
,TEST_DATA.ORIGINAL_INPUT_DATA
FROM
(
SELECT
--trim leading & trailing spaces before trying to process
--disallow extra spaces *within* the name
REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),' ',' '),' ',' ') AS FULL_NAME
,FULL_NAME AS ORIGINAL_INPUT_DATA
FROM
(
--if you use this, then replace the following
--block with your actual table
SELECT 'GEORGE W BUSH' AS FULL_NAME
UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
UNION SELECT 'TOMMY' AS FULL_NAME
UNION SELECT 'BILLY' AS FULL_NAME
UNION SELECT NULL AS FULL_NAME
UNION SELECT ' ' AS FULL_NAME
UNION SELECT ' JOHN JACOB SMITH' AS FULL_NAME
UNION SELECT ' DR SANJAY GUPTA' AS FULL_NAME
UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
UNION SELECT ' MRS SUSAN ADAMS' AS FULL_NAME
UNION SELECT ' MS AUGUSTA ADA KING ' AS FULL_NAME
) RAW_DATA
) TEST_DATA
) TITLE
) FIRST_NAME
Related Topics
How to Select the Last Record of a Table in SQL
Oracle Sql, Concatenate Multiple Columns + Add Text
Multiple Rows Output into Variables in MySQL
Alternative to Row_Number() to Get Row Position
Replace Default Null Values Returned from Left Outer Join
How Two Join to Tables Based on the Highest Value in One of Them
Remove Blank Line in Between Select Queries When Spooling to CSV File
Pg Copy Error: Invalid Input Syntax for Integer
How to Insert 1000 Rows At a Time
SQL Select Only Rows with Max Value on a Column
Force a Value of 0 for Non-Existing Value
Insert Multiple Rows Without Repeating the "Insert into ..." Part of the Statement
Multiplying Two Columns in SQL Server
Remove Numbers from String SQL Server
I Want to Give Serial No in My Query According to Group
Select Multiple Columns Count One Column and Group by One Column in One Table