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
What Is Self Join and When Would You Use It
Possible to Perform Cross-Database Queries With Postgresql
Ora-00918: Column Ambiguously Defined in Select *
Can Table Columns With a Foreign Key Be Null
How to Use Distinct and Order by in Same Select Statement
Physical Vs. Logical (Hard Vs. Soft) Delete of Database Record
Access-Sql: Inner Join With Multiple Tables
SQL Server: Get Table Primary Key Using SQL Query
Postgresql: Which Datatype Should Be Used for Currency
How to Delete Duplicate Entries
Strings as Primary Keys in SQL Database
Dynamically Create Columns Sql
Getting Only Month and Year from SQL Date