T-SQL Right Function default value

Question

I am working towards migrating an access Right() function onto SQL Server.

This is the Access Right() function:

Barcode: "ID" & (Right(String(8,"0") & [Job Number],8) & Right(String(6,"0") & 
[PART LIBARY HEADER_1]![PartID],6) & Right(String(6,"0") & [Part Assembly Link 
Table]![AssemblyLinkID],6))

What the above expression does, is that it dynamically creates a field to house a barcode that is always 22 characters in length. Now the values of these barcodes are determined by other columns. I am using the Right() method to gather the Rightmost: 8 chars of Job Number, 6 chars of PartID and 6 chars of AssemblyLinkID.

Now the problem I face is that whilst I can move these values onto T-SQL, I having difficulty setting the default value to be = 0.

For example the following input:

Job Number: 123456

PartID: 9876

AssemblyLinkID: 127

Would need to return… ID00123456009876000127

The String() function in access is handling the null values for me by appending with 0 but this method is not something available in SQL Server.

String(8,"0")

This is my SQL Code so far:

Concat('ID', RIGHT(STR(0, 8) & [Job Number],8)) AS Barcode,

STR() is not the function I am looking to use as it is not giving the same results as MS Access's String().

Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — Diado, Sep 03 '18 at 08:59
I would guess the function you are looking for, instead of `STRING`, is `REPLICATE`. For example, `REPLICATE('0',8)` would return `'00000000'`. — Thom A, Sep 03 '18 at 09:01

score 2 · Accepted Answer · answered Sep 03 '18 at 09:01

This does the job you want:

declare @t table ([Job NUmber] int, PartID int, AssemblyLinkID int)
insert into @t ([Job NUmber], PartID, AssemblyLinkID) values(123456,9876,127)

select
    'ID' + 
    RIGHT('00000000' + CONVERT(varchar(8),[Job Number]),8) +
    RIGHT('000000' + CONVERT(varchar(6),PartID),6) +
    RIGHT('000000' + CONVERT(varchar(6),AssemblyLinkID),6)
from @t

You can use REPLICATE as an exact analogue for your STRING calls but since the string literals are shorter than the method call, I prefer them.

Not sure quite what your point was around nulls, but if any of these columns may be NULL, then you may way to use COALESCE as well:

declare @t table ([Job NUmber] int, PartID int, AssemblyLinkID int)
insert into @t ([Job NUmber], PartID, AssemblyLinkID) values(123456,9876,127)
select
    'ID' + 
    RIGHT('00000000' + COALESCE(CONVERT(varchar(8),[Job Number]),''),8) +
    RIGHT('000000' + COALESCE(CONVERT(varchar(6),PartID),''),6) +
    RIGHT('000000' + COALESCE(CONVERT(varchar(6),AssemblyLinkID),''),6)
from @t

(Here we'll get all zeros for a NULL value. If you want something different, substitute that into the empty string towards the end of each expression)

I'm not declaring the table variable, but the SELECT did exactly what I wanted it to do! Thanks! Are there any benefits for declaring the variable table as opposed to just using the select? — Riku Das, Sep 03 '18 at 11:08
@RikuDas - it was just to make my example a complete runnable script. For future questions, you might consider providing your sample data in such a form - it's immediately copyable into a query window so that we can start writing a query and *test it*, without having any permanent effect on the database we're testing in. — Damien_The_Unbeliever, Sep 03 '18 at 11:10
I see that makes sense. I'm fairly new to SO but that is very useful to know. I'll bear it in mind. Thanks! — Riku Das, Sep 03 '18 at 12:29

score 1 · Answer 2 · answered Sep 03 '18 at 09:07

1

Right(String(8,"0") & [Job Number],8) =

RIGHT(REPLICATE('0', 8) + ISNULL([Job Number], ''), 8)

answered Sep 03 '18 at 09:07

Denis Rubashkin

2,151
1
9
14

T-SQL Right Function default value

2 Answers2