sql query using substring function in sqlserver

Lets take a example suppose there is table - "tbl_emp"
contain column like tbl_emp(first_name,Last_name,"column3","column4",...)

suppose one row contain data like this:

First_name, last _name , "column3","column4",...
amit , pathak ,'..',''

now in result if you want name will show as Apathak in place of amit pathak
we can use this Query using substring function of sqlserver


SELECT UPPER(SUBSTRING(FIRST_NAME,1,1)) + '' + LAST_NAME AS NAME FROM TBL_EMP

Result: Apathak

DETAIL OF SUBSTRING FUNCTION BELOW:
SUBSTRING ( STRING,START POSITION,NO OF CHARACTERS REQUIRED POSITION)

I more expale of SUBSTRING with CHARINDEX function

I have seen a problem of one person detail below.

i have string like 1017 , 9 , 10-06-2009 , 1|'5001','FORMAN S COMMISSION ','9','2','0','0',10-06-2009,1|'5002','DEFAULT INTEREST (CHITTY)','9','2','0','0',10-06-2009,1

i want to remove first part i.e 1017 , 9 , 10-06-2009 , 1
and i want to use rest part of the string....

Solution:

use substring function with charindex you will get string what you desire.......
i have seen problem there is "|" char find common in every column so on that behalf i have split the string and provide the need full result.

select substring('1017 , 9 , 10-06-2009 , 1|5001,FORMAN S COMMISSION', charindex( '|','1017 , 9 , 10-06-2009 , 1|5001,FORMAN S COMMISSION')+1, len('1017 , 9 , 10-06-2009 , 1|5001,FORMAN S COMMISSION')) from tbl_emp



DETAIL OF CHARINDEX FUNCTION BELOW:
SUBSTRING ( Pattern match,STRING)
result will be position of pattern where it matches.
Exp: SUBSTRING ( "M","AMIT Pathak") result will 2 because M is found on 2nd position in string "AMIT Pathak".