Jeff Moden (9/9/2013)

---

dwain.c (9/9/2013)

---

At the risk of being called a contrarian, this can be done without using RIGHT:

WITH SampleData (C1, C2) AS (

SELECT 1234, 1

UNION ALL SELECT 12345, 11

UNION ALL SELECT 123456, 111)

SELECT C1, C2

,STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))

FROM SampleData;

Perhaps RIGHT is the right way to go though it does take a lot of rows to make a difference.

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create and populate the test table on-the-fly.

SELECT TOP 1000000

C1 = ABS(CHECKSUM(NEWID()))%1000000

,C2 = ABS(CHECKSUM(NEWID()))%1000

INTO #TestTable

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

--===== Create a target variable that will take the display time out of the picture.

DECLARE @Bitbucket CHAR(9);

PRINT '========== Traditional RIGHT+RIGHT Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)

FROM #TestTable;

SET STATISTICS TIME OFF;

PRINT '========== Double STUFF Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))

FROM #TestTable;

SET STATISTICS TIME OFF;

PRINT '========== Integer Math RIGHT(RIGHT) Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)

FROM #TestTable;

SET STATISTICS TIME OFF;

Results:

(1000000 row(s) affected)

========== Traditional RIGHT+RIGHT Method ==========

SQL Server Execution Times:

CPU time = 499 ms, elapsed time = 496 ms.

========== Double STUFF Method ==========

SQL Server Execution Times:

CPU time = 702 ms, elapsed time = 697 ms.

========== Integer Math RIGHT(RIGHT) Method ==========

SQL Server Execution Times:

CPU time = 452 ms, elapsed time = 458 ms.