Hi vivek,
SELECT SUBSTRING(ageatdeath,0,CHARINDEX('Y',ageatdeath,0)) AS AGE_VAL,
SUBSTRING(ageatdeath,CHARINDEX('Y',ageatdeath,0),LEN(ageatdeath)) AS AGE_UNIT
this code also works fine.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/