Skip to content
Fix Code Error

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

March 13, 2021 by Code Error
Posted By: Anonymous

I have a string that is up to 3 characters long when it’s first created in SQL Server 2008 R2.

I would like to pad it with leading zeros, so if its original value was ‘1’ then the new value would be ‘001’. Or if its original value was ’23’ the new value is ‘023’. Or if its original value is ‘124’ then new value is the same as original value.

I am using SQL Server 2008 R2. How would I do this using T-SQL?

Solution

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

Answered By: Anonymous

Related Articles

  • Getting "requires numeric/complex matrix/vector…
  • changing value of threshold value when getting error…
  • error LNK2005: ✘✘✘ already defined in…
  • Regex needed to match a proper currency format of…
  • Simplest way to create Unix-like continuous pipeline…
  • Obtain most recent value for based on index in a…
  • insert tables in dataframe with years from 2000 to…
  • Regular expression to match numbers with or without…
  • Sort table rows In Bootstrap
  • Determine Number of Trailing Zeros of a Factorial…
  • How to print a number with commas as thousands…
  • How can i display data from xml api in flutter?
  • Form field border-radius is not working only on the…
  • Select bottom n records and concatenate in same row
  • R - sqldf [Impute the closest value between two…
  • "Keep Me Logged In" - the best approach
  • How do I limit the number of digits from 6 to 4 in…
  • Tips for Aggregating MongoDB Time Trend Data over…
  • Trouble using ROW_NUMBER() OVER (PARTITION BY ...)
  • python how to pad numpy array with zeros
  • How can I profile C++ code running on Linux?
  • How to perform a conditional groupby calculation in…
  • How to change the color of vaadin-select-text-field…
  • create custom week variable based on cycle variable in R
  • Use two shapefiles in geopandas
  • DataTable draw daterange from vaadin-date-picker in polymer
  • What are the undocumented features and limitations…
  • How does database indexing work?
  • Print the significance of the relationship and…
  • Comparing two dataframes with some entries missing
  • SQL Server query to find rows that have no parent…
  • SMTP error 554
  • exec failed because the name not a valid identifier?
  • PostgreSQL: No function matches the given name and…
  • How to reduce array of objects by value
  • Fastest way to iterate over all the chars in a String
  • Smart way to truncate long strings
  • How do I include certain conditions in SQL Count
  • Validate that a string is a positive integer
  • Cant figure out what Im doing wrong. Unhandled…
  • Generate SQL Create Scripts for existing tables with Query
  • SQL NVARCHAR and VARCHAR Limits
  • Create comma separated list from string but ignore…
  • How do I keep only the first map and when the game…
  • How to return only the Date from a SQL Server…
  • long long int vs. long int vs. int64_t in C++
  • What is a plain English explanation of "Big O" notation?
  • ValueError: could not broadcast input array from…
  • Is it possible to apply CSS to half of a character?
  • How to compare two lists of lists in a dataframe?
  • Select SQL Server database size
  • The conversion of the varchar value overflowed an int column
  • How to find column names for all tables in all…
  • What is the maximum possible length of a query string?
  • How can you profile a Python script?
  • Python3 error trying to decode (base64) then…
  • Extracting the top 5 maximum values in excel
  • How to convert image into byte array and byte array…
  • SQL Server - XQuery: delete parent node based on…
  • Copy a file in a sane, safe and efficient way
  • Pandas / Python - Compare numerically each row with…
  • AES/CBC/PKCS5 Padding | Alternate to…
  • Is there a JavaScript function that can pad a string…
  • Jetty server throws idle timeout for REST calls
  • Js Calculation based on radio button, and some problems
  • WHERE ISNULL doesn't bring though records where…
  • Best way to do multi-row insert in Oracle?
  • How to dynamically add and remove views with Ember.js
  • How to update a rating sequentially?
  • How to find which columns contain any NaN value in…
  • How to print and sort list of sellers based on Bonus…
  • How to handle Vue 2 memory usage for large data (~50…
  • How to apply rolling t.test with pandas?
  • How to remove leading and trailing zeros in a string? Python
  • Failed to authenticate on SMTP server error using gmail
  • Javascript array sort and unique
  • Improve INSERT-per-second performance of SQLite
  • How to check if any value is NaN in a Pandas DataFrame
  • Continuous Convolution Using 3 Different Methods in…
  • Change in pandas Series.tz_convert behaviour in v1.X…
  • Vue JS and appending a variable to end of a URL
  • How to split and modify a string in NodeJS?
  • Vue + VUEX + Typescript + Vue Router. component not…
  • SQL query return data from multiple tables
  • Polymer 1.0 'array-style' path accessors,…
  • Google in-app billing, a toast breaks everything
  • Conditional WHERE clauses with TVP parameter
  • return two dimensional array from function with…
  • Equals(=) vs. LIKE
  • How to align flexbox columns left and right?
  • generate days from date range
  • How to add leading zeros?
  • How to ISO 8601 format a Date with Timezone Offset…
  • TypeError: only integer scalar arrays can be…
  • Adding extra zeros in front of a number using jQuery?
  • The definitive guide to form-based website authentication
  • Persistent invalid graphics state error when using ggplot2
  • Select Tag Helper in ASP.NET Core MVC
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • What is the difference between "long", "long long",…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How to write a switch statement in Ruby

Next Post:

How does database indexing work?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error