Results 1 to 4 of 4

Thread: t-sql

  1. #1
    Join Date
    Jul 2003
    Posts
    8

    Unanswered: t-sql

    In the table [ID] store

    id
    ----
    1
    2
    3
    4
    20
    111


    how can I use the SQL to convert the data like this.

    id
    ----------
    0001
    0002
    0003
    0004
    0020
    0111

  2. #2
    Join Date
    Jul 2003
    Posts
    55

    Re: t-sql

    Hi,
    I believe that there is no direct way to do this. I suggest to implement a function that makes the required format.
    Code:
    CREATE FUNCTION  MyFormat_Fn 
    	(@A_1 int)
    RETURNS [varchar](4)
    
    AS
    BEGIN
    DECLARE @RetVal_lv [varchar](4)
    DECLARE @Len_lv [int]
    
    select  @Len_lv = len(convert(varchar(4),@A_1)) 
    select @RetVal_lv = @A_1
    while @Len_lv < 4
    begin
    	select @RetVal_lv =  '0' + @RetVal_lv
    	select  @Len_lv = @Len_lv + 1
    end
    
    return @RetVal_lv
    END
    Then you can do:
    Code:
    SELECT     dbo.MyFormat_Fn(T_ID)
    FROM         Table1
    If you have better option, please let me know
    Good luck,

    Originally posted by peterchoy
    In the table [ID] store

    id
    ----
    1
    2
    3
    4
    20
    111


    how can I use the SQL to convert the data like this.

    id
    ----------
    0001
    0002
    0003
    0004
    0020
    0111

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    peterchoy if your datatype is a v/char type try:

    select right('0000' + '1',4)
    , right('0000' + '20',4)
    , right('0000' + '111',4)
    , right('0000' + '1234',4)
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select replicate('0', 4-datalength(cast(id as varchar(10))) + cast(id as varchar(10))

Posting Permissions

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