CODEWITHSUNDEEP
sql-servert-sqlstring-formattingstring-concatenation
Wes Palmer
Wes Palmer

Reputation: 880

Dynamic Concatenation

I am working on developing a table and I a running into an issue I might be going about this the wrong way but I have hit a road block. I have and ID in my table that I need to update it is an NVARCHAR(12) Field and currently it has numbers in it. I need to format it like the following: TP0000000001, TP0000000002,...,TP0000245632,...,TP9999999999. I just need to find a way to append TP and trailing Zeros plus the ID number where the whole string adds up to 12. I am currently using a while loop with some hard coded information but I need it to be dynamic since I have about 14000 records to do an update on.

Here is the update I would use:

UPDATE Vendor
SET TPPRM_ID = <The Formatted Value>

Here is the code that currently formats the number to Correctly with hard coding the column I am needing to format is call TPPRM_ID as you can see in the update. Any help on this would be greatly appreciated.

DECLARE @Var NVARCHAR(12)
DECLARE @Counter INT
SET @VAR = 'TP'
SET @Counter = 1

WHILE @Counter <= 12
    BEGIN
        SET @VAR = @VAR + '0'
        SET @Counter = @Counter + 1

        IF LEN(@Var) >= 10 
        BEGIN
            SELECT @VAR +  '10'
            PRINT @VAR
            BREAK
        END

        --PRINT @VAR
    END

Upvotes: 0

Views: 35

Answers (2)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Another solution is -

SELECT 'TP' + REPLACE(STR(TPPRM_ID, 10), ' ', 0) 
FROM Vendor

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32230

Just overpad the zeroes and then cut off the rightmost 10 characters. Then append 'TP'.

SELECT 'TP' + RIGHT(REPLICATE('0',10) + TPPRM_ID),10)
FROM Vendor

Upvotes: 1

Related Questions