Here is the sample database, table, and data needed.
-- Database Creation CREATE DATABASE SQLAuthority; GO USE SQLAuthority; GO -- Table Creation CREATE TABLE EMPLOYEE (ID INT IDENTITY(1,1), NAME VARCHAR(100)); GO -- Sample Data INSERT INTO EMPLOYEE (NAME) VALUES('PINAL DAVE'); GO INSERT INTO EMPLOYEE (NAME) VALUES('CHETAN DAVE'); GO INSERT INTO EMPLOYEE (NAME) VALUES('BHIM RAO'); GO INSERT INTO EMPLOYEE (NAME) VALUES('SRK SINGH'); -- Have a look at data SELECT * FROM EMPLOYEE
We are storing first name and last name together in one column called “NAME”. we need to get the only first name using a query.
Here are two workable solutions.
SELECT LEFT(Name, CHARINDEX(' ', Name + ' ') - 1) AS FirstName FROM EMPLOYEE
SELECT SUBSTRING(Name, 0, CHARINDEX(' ', Name, 0)) AS FirstName FROM EMPLOYEE
What are the other practical solutions without using CHARINDEX AND SUBSTRING?
Here are few additional blog posts which can help you to answer this puzzle:
- SQL SERVER – Find First Non-Numeric Character from String
- SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX
- SQL SERVER – Finding the Occurrence of Character in String
Please post your answer in the comment section. I will publish all the valid answers on next Monday. Please share this with your friends and see if they can solve this puzzle.
Reference: Pinal Dave (https://blog.sqlauthority.com)