Replace a string with an integer value keeping the string length fixed

Question

I have two columns in SQL Server Table, one is Id and other one is Number, for example if Ids are as follows: 1189, 758, 756, I want a User defined or system defined function, that can generate numbers as: T01189, T00758, T00756.

I want to keep this function either as Default field or add it to trigger - soon after the record inserted, you can suggest me which one is better also.

Tim Biegeleisen · Accepted Answer · 2020-07-14 03:00:25Z

2

You could use a padding trick here:

SELECT
    Id,
    'T' + RIGHT('0000' + Id, 5) AS Number
FROM yourTable;

Demo

answered Jul 14, 2020 at 3:00

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abdul Over a year ago

But this is giving me the correct answer maybe because of different DB or version: select 'T' + RIGHT('00000'+CAST(InspectionItemId AS VARCHAR(5)),6), InspectionItemId, InspectionItemNumber from InspectionItems where CaseId=293 order by 1 desc

Tim Biegeleisen Over a year ago

Oh...I am assuming that Id is text already. If not, then yes, you would need to cast it first, as you have pointed out.

Collectives™ on Stack Overflow

Replace a string with an integer value keeping the string length fixed

1 Answer 1

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related