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'
Village='Kodur'
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
code='SAKOA14'.
Here 14 is the current year last two digits.
Please tell me how can I write query please help me.......
Posted

SQL
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
 
v2
Hi,

Use below syntax

SQL
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