locked
Prefixing 0 (zero) before data RRS feed

  • Question

  • Hi,

    Can someone please help with this below scenario ? Thanks,

    I have a SQL server database table in which there is a field called "Day" which has value from 1 to 9

    Column name:

    Day

    1

    2

    3

    4

    5

    6

    7

    8

    9

    Now, I want to make it like 01, 02, 03, 04...09 

    means need to update the data for this column so that 0 is prefixed with it. What would be the best approach to do this?

    Regards,

    Thursday, May 9, 2013 9:26 AM

Answers

  • It means you have to define the column as a varchar column, or you can't do this. You cannot do this against one int column.

    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Gert-Jan Strik Thursday, May 9, 2013 10:17 AM
    • Marked as answer by UltraDev Friday, May 10, 2013 4:04 AM
    Thursday, May 9, 2013 9:39 AM
  • Without data type conversion this cant be acheived.

    Declare @testint int='01',@teststring varchar(10)='01'
    select @testint as '@testint' ,@teststring as '@teststring'

    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by UltraDev Thursday, May 16, 2013 11:18 AM
    Thursday, May 9, 2013 10:29 AM

All replies

  • If column an integer type then you will not be able to update it, to prefix 0 data type must be char.

    SELECT '0'+cast(1 AS varchar(2))

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Thursday, May 9, 2013 9:29 AM
    Thursday, May 9, 2013 9:29 AM
  • SELECT RIGHT('00'+ CONVERT(VARCHAR,YourColumnName),2) 

    FROM TableName


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Thursday, May 9, 2013 9:34 AM
  • It means you have to define the column as a varchar column, or you can't do this. You cannot do this against one int column.

    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Gert-Jan Strik Thursday, May 9, 2013 10:17 AM
    • Marked as answer by UltraDev Friday, May 10, 2013 4:04 AM
    Thursday, May 9, 2013 9:39 AM
  • Thanks for the replies,

    but the column is INT only. What could be the workaround or alternate for this ? 

    I need this data to be updated likewise I mentioned.

    Regards,

    Thursday, May 9, 2013 9:39 AM
  • Thanks for the replies,

    but the column is INT only. What could be the workaround or alternate for this ? 

    I need this data to be updated likewise I mentioned.

    Regards,

    We cannot do this to one INT column.

    Many Thanks & Best Regards, Hua Min

    Thursday, May 9, 2013 9:39 AM
  • Why do you want to do this? 


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Thursday, May 9, 2013 9:44 AM
  • Hi Dev, As you said its INT datatype, you can think of such formatting being handled in your application layer.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Sarat (SS) Thursday, May 9, 2013 10:05 AM
    Thursday, May 9, 2013 9:48 AM
  • Is there Not any way to do this using SSIS 2005 even ?

    Thanks

    Thursday, May 9, 2013 10:14 AM
  • Without data type conversion this cant be acheived.

    Declare @testint int='01',@teststring varchar(10)='01'
    select @testint as '@testint' ,@teststring as '@teststring'

    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by UltraDev Thursday, May 16, 2013 11:18 AM
    Thursday, May 9, 2013 10:29 AM