Professional Web Applications Themes

left padding with zeros - Microsoft SQL / MS SQL Server

I have a variable 'v_dept' of int data type. I have to make it a string of length 4 'v_deptS' with left padding v_dept with zeroes. Its like if v_dept = 5 then i want v_deptS = '0005' but if v_dept = 56 then i want v_deptS = '0056'. I have already done it by this : SELECT v_deptS = case len(v_dept) when 1 then '000' + cast (v_dept as varchar(1)) when 2 then '00' + cast (v_dept as varchar(2)) when 3 then '0' + cast (v_dept as varchar(3)) else cast (v_dept as varchar(4)) end It is working ok but ...

  1. #1

    Default left padding with zeros

    I have a variable 'v_dept' of int data type. I have to make it a string of
    length 4 'v_deptS' with left padding v_dept with zeroes. Its like if
    v_dept = 5 then i want v_deptS = '0005' but if v_dept = 56 then i want
    v_deptS = '0056'. I have already done it by this :

    SELECT v_deptS = case len(v_dept)
    when 1 then '000' + cast (v_dept as varchar(1))
    when 2 then '00' + cast (v_dept as varchar(2))
    when 3 then '0' + cast (v_dept as varchar(3))
    else cast (v_dept as varchar(4))
    end

    It is working ok but I am looking for a better solution so that if i have to
    make it a string of length 14 I dont have to type so much code.

    Thanks

    Rizwan


    Rizwan Guest

  2. #2

    Default Re: left padding with zeros

    A little bit awkward solution, but I don't know anything
    better:

    SELECT v_deptS =
    REPLACE(SPACE(4-LEN(v_dept)), ' ', '0') + CAST(v_dept AS
    varchar)

    Michal Guest

  3. #3

    Default Re: left padding with zeros

    I come up with this solution :

    SELECT v_deptS = REPLACE (STR (v_dept, 4), ' ', '0')

    "Michal Chaniewski" <com.pl> wrote in message
    news:0d3d01c34af0$91f70f80$gbl... 


    Rizwan Guest

  4. #4

    Default Re: left padding with zeros

    SELECT RIGHT('0000'+CAST(v_dept AS VARCHAR(4)),4)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. padding question
    By rmorgan in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 22nd, 07:58 PM
  2. How do you get rid of padding???
    By 32jln in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: January 6th, 04:28 AM
  3. Disappering leading zeros
    By elvis is dead in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 13th, 11:23 AM
  4. cryptostream,padding
    By Fred Herring in forum ASP.NET Security
    Replies: 0
    Last Post: January 17th, 04:01 PM
  5. padding numbers
    By Ike in forum PHP Development
    Replies: 3
    Last Post: August 5th, 09:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139