Charindex and Left function in detail. How to find firstname and surname using this.

<<LETS UNDERSTAND THE CONCEPT OF CHARINDEX AND LEFT FUNNCTION>>

CREATE TABLE FRIENDS

(

ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

NAME VARCHAR(50)

)

INSERT INTO FRIENDS(NAME)

SELECT 'ANURAG NAYAK'

UNION ALL SELECT 'ABHISHEK NAYAK'

UNION ALL SELECT 'SIBA PRASAD DALAI'

UNION ALL SELECT 'MUKESH MALLICK'

SELECT * FROM FRIENDS

--1   ANURAG NAYAK

--2   ABHISHEK NAYAK

--3   SIBA PRASAD DALAI

--4   MUKESH MALLICK

--REQUIRED OUTPUT IS LIKE

--ANURAG

--ABHISHEK

--SIBA

--MUKESH

--LEFT FUNCTION

SELECT NAME,LEFT(NAME,6) AS [FIRST NAME]

FROM FRIENDS

--NAME                 FIRST NAME   

--ANURAG NAYAK         ANURAG

--ABHISHEK NAYAK       ABHISH

--SIBA PRASAD DALAI    SIBA P

--MUKESH MALLICK       MUKESH

--LOOK AT THE FIRST NAME COLUMN...IT WORKS FINE FOR ANURAG AND MUKESH ,,,,BUT WE ARE NOT GETTING THE FIRST NAME FOR SIBA ABHISHEK

--FIND THE FIRST NAME

--SO WE NEED TO FIND OUT THE SPACE WHERE IT IS COMING

SELECT NAME,CHARINDEX(' ',NAME)

FROM FRIENDS

--SO SPACE IS AT 7,9,5 AND 7TH PLACE FOR ALL THOSE NAMES

--ANURAG NAYAK                 7

--ABHISHEK NAYAK               9

--SIBA PRASAD DALAI            5

--MUKESH MALLICK               7

--FINAL OUTPUT IS LIKE THIS

SELECT NAME,LEFT(NAME,CHARINDEX(' ',NAME)-1) AS [FIRST NAME]

FROM FRIENDS

--NOW LETS FIND OUT THE SURNAME OF ALL

--I HAVE COME UP WITH A IDEA LIKE THIS..TRY TO UNDERSTAND IT..ITS VERY EASY…THE BELOW QUERY LOOKS BIT MESSY AND COMPLEXT..BUT I PROMISE ITS VERY EASY TO UNDERSTAND

SELECT (NAME),REVERSE(LEFT(REVERSE(NAME), CHARINDEX(' ',REVERSE(NAME))-1)) FROM FRIENDS

--EXPLANATION STEP BY STEP WISE

  SELECT REVERSE(NAME) FROM FRIENDS

--STEP 1[REVERSE THE NAME]

--KAYAN     GARUNA

--KAYAN     KEHSIHBA

--IALAD     DASARP      ABIS

--KCILLAM   HSEKUM

--STEP 2[REVERSE THEN FIND THE SPACE FROM LEFT…]

--NOW LETS FIND OUT THE SPACE...

--KAYAN GARUNA          5

--KAYAN KEHSIHBA        5

--IALAD DASARP ABIS     5

--KCILLAM HSEKUM        7

--STEP 3[USE LEFT FUNCTION TO FIND OUT THE FIRST NAME TILL SPACE..LIKE  KAYAN,KAYAN,IALAD,KCILLAM]

--SO NOW WE CAN USE LEFT FUNCTION...

 SELECT REVERSE(NAME),LEFT (REVERSE(NAME),CHARINDEX(' ',REVERSE(NAME))-1) FROM FRIENDS

--KAYAN GARUNA                KAYAN

--KAYAN KEHSIHBA              KAYAN

--IALAD DASARP ABIS           IALAD

--KCILLAM HSEKUM              KCILLAM

--STEP 4[REVERSE THE NAME TO GET THE SURNAME]

--NOW WE NEED THE SECOND COLUMN IN REVERSE WAY... JUST DO REVERSE

SELECT REVERSE(NAME),REVERSE(LEFT (REVERSE(NAME),CHARINDEX(' ',REVERSE(NAME))-1)) AS SURNAME FROM FRIENDS

   J WE WILL COME WITH NEW CONCEPT....HOPE YOU ENJOYED IT J

              J J J  TILL THEN BYE J J J