Most Efficient T-SQL Way to Pad a Varchar on the Left to a Certain Length

Most efficient T-SQL way to pad a varchar on the left to a certain length?

This is simply an inefficient use of SQL, no matter how you do it.

perhaps something like

right('XXXXXXXXXXXX'+ rtrim(@str), @n)

where X is your padding character and @n is the number of characters in the resulting string (assuming you need the padding because you are dealing with a fixed length).

But as I said you should really avoid doing this in your database.

Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

If the field is already a string, this will work

 SELECT RIGHT('000'+ISNULL(field,''),3)

If you want nulls to show as '000'

It might be an integer -- then you would want

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg '0000' and VARCHAR(4)),4

Most efficient: Cut off varchar after first space?

Notice the +' ' this will trap any single word

Example

Declare @S varchar(max) = 'FIRST_STRING SECOND_STRING THIRD_STRING'

Select left(@S,charindex(' ',@S+' ')-1)

Returns

FIRST_STRING

Integer PadLeft function in T-SQL

I believe this may be what your looking for:

SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0') 

FROM tableA

or

SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]

FROM tableA

I haven't tested the syntax on the 2nd example. I'm not sure if that works 100% - it may require some tweaking - but it conveys the general idea of how to obtain your desired output.

EDIT

To address concerns listed in the comments...

@pkr298 - Yes STR does only work on numbers... The OP's field is an ID... hence number only.

@Desolator - Of course that won't work... the First parameter is 6 characters long. You can do something like:

SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA

this should theoretically move the goal posts... as the number gets bigger it should ALWAYS work.... regardless if its 1 or 123456789...

So if your max value is 123456... you would see 0000123456 and if your min value is 1 you would see 0000000001

why this query added extra length upper(trim(LEFT(col_name::varchar, 100)))

The LEFT(string, N) function returns N characters from the left side of a string.

However, those N characters may take more than N bytes (octects), for example accented characters:

dbadmin=> select left('é', 1);
left
------
é

dbadmin=> select octet_length(left('é', 1));
octet_length
--------------
2

SQL Server CONVERT int to varchar with padding

Wrap it in:

right('0' + convert(...), 2)

Alternatively:

right(100 + datepart(ww, ...), 2)

How to pad a number with zero?

SELECT RIGHT('0000'+ISNULL(field,''),4)


Related Topics



Leave a reply



Submit