Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    104

    Unanswered: convert with stripping leading zeros

    Hello All,

    Can anyone please tell me any workaround how to achieve this

    select convert(int,"0123")

    resultset as
    0123

    I dont want to strip leading zeros

    thanx in advance

    cheers,
    --Pooja

  2. #2
    Join Date
    Aug 2002
    Posts
    13
    Hello All,

    Can anyone please tell me any workaround how to achieve this

    select convert(int,"0123")

    resultset as
    0123

    I dont want to strip leading zeros

    thanx in advance

    cheers,
    --Pooja
    Hi Pooja,

    If you want to convert a character string into a numeric value? In my knowledge it's not posible to prevent stripping the leading zero's. Anyone correct me i'f i'm wrong.

    Can you tell me why you want to do that? If I have some more information mayby I can help you....

    Greetz.

    Natas

  3. #3
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Stange output by you !

    By me :
    Code:
    1> select convert(int,"0123")
    2> go
    
     -----------
             123
    
    (1 row affected)
    1> select @@version
    2> go
    
    
     -----------------------------------------------------------------------------------
     Adaptive Server Enterprise/11.9.2.6/1290/P/EBF 10488 ESD1/HP9000-735/HP-UX 10.2/FBO/Mon Jul 15 09:57:55 2002
    
    
    (1 row affected)

  4. #4
    Join Date
    Feb 2004
    Location
    India
    Posts
    12
    Hi Pooja,

    If you are converting it to 'int', you won't get the result you want. Instead, you need to convert the number to character string and prefix the leading zeros.

    For example:

    To get a 5 digit output, with leading zeros, try following query

    select convert(char(5),replicate('0',5-datalength('123'))+convert(varchar,'123'))

    '123' can be replaced by the actual column name in a table.


    Thanks and Regards,
    Prabhanjan

  5. #5
    Join Date
    Dec 2002
    Posts
    104
    thanx all,

    I know this while converting char to int leading zero are stripped.

    reason i dont want to loose leading zeros is "0123" represents code and 123 can be other code.

    --Pooja

  6. #6
    Join Date
    Aug 2002
    Posts
    13
    Pooja,

    I understand that you don't want to lose the leading zero's because the value is a code. And the output "123" mayby a another code in the table.

    Ok, don't call me stupid but why converting? Can you tell me why do you want to convert? Do you want to put the data in a new table? Do you wat to select? Stored procedure? If you tell us mayby we can help you.

    Natas

  7. #7
    Join Date
    Dec 2002
    Posts
    104
    thanx Natas,

    Actually the value has to be inserted into table which has integer datatype.
    I think there is no workaround for this....

    i have to change the table schema...to change datatype of that field.

    thanx alot for all ur help.

    cheers,
    --Pooja

Posting Permissions

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