Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    18

    Question Unanswered: Translation question

    Hi !

    I'm not very good in SQL but I have a lot of translation to do from Oracle to SQLServer.
    SO I don't know how to translate something like :
    TO_CHAR(Value, '99090')

    Value is a number.

    Can anybody help ?
    Thanks
    Last edited by Linirlan; 07-21-03 at 09:18.

  2. #2
    Join Date
    Jul 2003
    Location
    Melbourne
    Posts
    3

    Re: Translation question

    Originally posted by Linirlan
    Hi !

    I'm not very good in SQL but I have a lot of translation to do from Oracle to SQLServer.
    SO I don't know how to translate something like :
    TO_CHAR(Value, '99090')

    Value is a number.

    Can anybody help ?
    Thanks
    Hi,

    Try this out... Cast(@variableName as Varchar(100))

    HTH,

  3. #3
    Join Date
    Jul 2003
    Posts
    18
    Sorry but I don't understand the utility of a varchar(100) -that's pretty big for my 5 digits value-.

    I juste want to reproduce in SQLServer the behavior of something like :
    SELECT TO_CHAR(12345,'99090') FROM DUAL

    Hope the question is clear...

  4. #4
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Surely the post of SamratValani was only an example to show you the CAST function :-)

    You can cast your value to any kind of data-type you need.

    To better undestand what you need to do - since i do not know Oracle so well - can you post even the result that the query you posted

    SELECT TO_CHAR(12345,'99090') FROM DUAL

    will produce?
    Davide Mauri
    http://www.davidemauri.it

  5. #5
    Join Date
    Jul 2003
    Posts
    18
    Here's the documentation

    With : SELECT TO_CHAR(12345,'09999') FROM DUAL
    The result will be : 12345

    The second parameter of the 'TO_CHAR' instruction is a "style" parameter.

    For instance, the number of digits of that "style" parameter will be the number of digits returned.
    '0999' Returns leading zeros.
    '9990' Returns trailing zeros.

    (My example '99090' was a mistake, sorry)

    So do you know how to return leading and trailing zeros ?

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    In these examples the returned value must be 5 characters, if your aplication is different just change the number of '0' in quotes and the number 5 to your required value.
    Code:
    SELECT RIGHT('00000' + CAST(1234 AS VARCHAR) ,5)
    SELECT LEFT(CAST(1234 AS VARCHAR) + '00000'  ,5)
    Output
    -----
    01234

    -----
    12340

    The first one returns leading zeros the second returns trailing. If your are adventurous you could write your own User Defined Function (UDF).
    MCDBA

  7. #7
    Join Date
    Jul 2003
    Posts
    18

    Smile

    Thanks for help.

Posting Permissions

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