Quick thought, this can easily be stuffed

😎

;WITH BASE_ID AS

( SELECT BID FROM

(VALUES (4461),(14130),(23891)) AS X(BID)

)

SELECT

BI.BID

,STUFF('CP000000000000'

,14 - LEN(CAST(BI.BID AS VARCHAR(12)))

,LEN(CAST(BI.BID AS VARCHAR(12)))

,CAST(BI.BID AS VARCHAR(12))

) AS UNIQUE_LINK

FROM BASE_ID BI

Results

BID UNIQUE_LINK

----------- ---------------

4461 CP000000044610

14130 CP000000141300

23891 CP000000238910