Convert number to varchar in SQL with formatting

Question

Is there a way in T-SQL to convert a TINYINT to VARCHAR with custom number formatting  For instance  my TINYINT has a value of 3 and I want to convert it to a VARCH of 03  so that it always shows a 2 digit number   I don t see this ability in the CONVERT function

User · Answer

You can try this  DECLARE  Table TABLE          Val INT    INSERT INTO  Table SELECT 3 INSERT INTO  Table SELECT 30  DECLARE  NumberPrefix INT SET  NumberPrefix   2 SELECT  REPLICATE  0    NumberPrefix - LEN Val     CAST Val AS VARCHAR 10   FROM     Table

User · Answer

declare  t  tinyint set  t  3 select right replicate  0   2    cast  t as varchar  2    Ditto  on the cripping effect for numbers   99  If you want to cater for 1-255 then you could use  select right replicate  0   2    cast  t as varchar  3    But this would give you 001  010  100 etc

User · Answer

What is the value range  Is it 0 through 10  If so  then try   SELECT REPLICATE  0  2-LEN  t     CAST  t AS VARCHAR    That handles 0 through 9 as well as 10 through 99   Now  tinyint can go up to the value of 255  If you want to handle   99 through 255  then try this solution   declare  t  TINYINT set  t  233 SELECT ISNULL REPLICATE  0  2-LEN  t         CAST  t AS VARCHAR    To understand the solution  the expression to the left of the   calculates the number of zeros to prefix to the string   In case of the value 3  the length is 1  2 - 1 is 1  REPLICATE Adds one zero  In case of the value 10  the length is 2  2 - 2 is 0  REPLICATE Adds nothing  In the case of the value 100  the length is -1 which produces a NULL  However  the null value is handled and set to an empty string   Now if you decide that because tinyint can contain up to 255 and you want your formatting as three characters  just change the 2-LEN to 3-LEN in the left expression and you re set

User · Answer

Here s an alternative following the last answer  declare  t tinyint  v tinyint set  t 23 set  v 232 Select replace str  t 4       0   replace str  t 5       0     This will work on any number and by varying the length of the str   function you can stipulate how many leading zeros you require  Provided of course that your string length is always    maximum number of digits your number type can hold

User · Answer

Correci  n  3-LEN  declare  t  TINYINT  set  t  233 SELECT ISNULL REPLICATE  0  3-LEN  t         CAST  t AS VARCHAR

User · Answer

RIGHT  00    CONVERT VARCHAR  MyNumber   2    Be warned that this will cripple numbers   99  You might want to factor in that possibility

User · Answer

Had the same problem with a zipcode field  Some folks sent me an excel file with zips  but they were formatted as   s  Had to convert them to strings as well as prepend leading 0 s to them if they were  lt  5 len      declare  int tinyint set      int   25 declare  len tinyint set      len   3  select right replicate  0    len    cast  int as varchar 255     len    You just alter the  len to get what you want  As formatted  you ll get     001 002     010 011     255   Ideally you d  varchar  len    too  but that blows up the SQL compile  Have to toss an actual   into it instead of a var

User · Answer

Use the RIGHT function    e g   DECLARE  testnum TINYINT SET  testnum   3 PRINT RIGHT  00    CONVERT VARCHAR 2    testnum   2

[sql] Convert number to varchar in SQL with formatting

Examples related to sql

Examples related to tsql