Click here to Skip to main content
15,938,218 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
I have three columns in a table like name,village,state.
Name='Samba Siva'
State='Andra pradesh'

I want to cancatenate three columns like first two characters of first column and first characters of second column and First character of third column and current year last two digits.

I have tried like this

select right(replace(name,2)+Cast(cast(coalesce(max(village),0) as varchar) as varchar),2) customercode
from(select top 1 customercode from K_FS_DistributorDetails order by customercode)r;

but its not working. i.e: I want output like
Here 14 is the current year last two digits.
Please tell me how can I write query please help me.......

SELECT TOP 1 UPPER(LEFT(Name, 2) + LEFT(Village, 2) + LEFT(State, 1) + CONVERT(VARCHAR, YEAR(GETDATE()) % 100)) code
  FROM K_FS_DistributorDetails
 ORDER BY customercode DESC

Try this ,
I also checked in SQL Fiddle..
Demo SQL Fiddle
Share this answer

Use below syntax

Select left(isnull(Name,''),2) + left(isnull(Village,''),1) + left(isnull(State,''),1) + Right(Year(getdate()),2) From ......Your Query
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900