Hi ALL

To day i am going to use the Replicate.
I am having the requirement like i need to genrate a pattern(employeeNumber) ,for this pattern we need to use two columns id and department id

Output:

        If  id is 1 and departmentid is 1 then i need out put like YH0010001

Rules:

1) first two characters(YH) are common for everyone.
2) next three digits should be department number(it having max three digit only ,if it is 1 u need to represent          as 001,if it is 2 then 002 ,if it is 23 then 023, if it 333 then 333 )
3) next four digits are id of the employee(it having max four digit only ,if it is 1 u need to represent as 0001,if it is 2 then 0002 ,if it is 23 then 0023, if it 333 then 0333 )

If id lenth is less than than the four digits then u need to keep Zero's
If Department number lenth is less than the three digits then u need to keep Zero's

so follow the above conditions and write a SQL.

DATA:

DECLARE @EMPLOYEE TABLE (ID VARCHAR(30),EMPNAME VARCHAR(30),DEPTID VARCHAR(30))
INSERT INTO @EMPLOYEE VALUES('1','SANTOSH','1')
INSERT INTO @EMPLOYEE VALUES('2','SUDEEP','2')
INSERT INTO @EMPLOYEE VALUES('33','KISHOR','12')
INSERT INTO @EMPLOYEE VALUES('444','PHANI','333')
INSERT INTO @EMPLOYEE VALUES('12','GAURAV','1')
INSERT INTO @EMPLOYEE VALUES('222','PRADEEP','2')
INSERT INTO @EMPLOYEE VALUES('4444','KRISHNA','3')


SELECT * FROM @EMPLOYEE

I NEED SECOND RESULT SET......


FINAL QUERY:


SELECT *,'YH'+REPLICATE('0',3-LEN(DEPTID))+DEPTID+REPLICATE('0',4-LEN(ID))+ID AS EMPLOYEEID FROM @EMPLOYEE

Explanation: 
    
for getting this result i used replicate which is a string function using this u can repeat the value for how many time s u want ....



THANKS FOR VISITING BLOG :)