Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Question Unanswered: Small Oracle2SQLserver Conversion problem

    Dear all,

    I have a small code within a PL/SQL Oracle function which I want to translate for use in SQL Server. The translation shud be as brief as possible.
    The code portion is:

    RETURN TO_CHAR(IN_HH,'fm09')

    where IN_HH is a local INT type variable.
    Specifically, what wud be the shortest possible SQL Server equivalent code for TO_CHAR(IN_HH,'fm09'). Pleez, any suggestions?

    Thanx in advance
    Last edited by KnightHasan; 11-19-04 at 08:17.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    check convert() in bol

  3. #3
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Exclamation Thx but...

    Thx Harshal,

    But cud u be more specific plz. I think I missed the bus somewhere...

    My actual problem is not as much in conversion of int to varchar but more in the formatting part. The conversion and formatting should be done in one go as would otherwise be possible in Oracle...

    TO_CHAR(IN_HH, 'fm09')

    How to accomplish this seemingly insignificant task ( ) in SQL Server?

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    hi,

    I m no good at oracle. so donot know what the function
    TO_CHAR(IN_HH, 'fm09') does. If u could specify what kind of formatting u r expecting then it would be more easier...

  5. #5
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Cool

    Hi!

    The Oracle function TO_CHAR converts any datatype to CHAR. In my main question, there are 2 parameters being provided to TO_CHAR.

    The first, which is mandatory, is the expression that is to be converted. In my case, it is a local variable named as IN_HH of datatype INT.

    The second parameter, which is optional specifies the formatting that is to be performed on the converted type. fm09 formats the resulting value into a two digit value. This is done thru the 09 part.
    BTW, just FYI, the stage at which this conversion is being performed, the variable will be containing a one or a two digit number only. This has been ensured in the preceding code of my function(which I have not shown here)
    So, if IN_HH contains for instance, 1, the result of this conversion will be 01 and if it contains, 12 for instance, I will get 12 as a result.
    Moving on to 'fm', this removes the preceding space(single )from the result. When Oracle uses TO_CHAR, to convert the datatype INT, it introduces a preceding space in the result, if the variable contained a +ve number, and it includes a '-' sign in place of the space, if the variable contained a -ve number.
    So, to summarize,
    Value contained in IN_HH = 5
    The result of TO_CHAR(IN_HH, 'fm09') would be ---->
    ' 05'
    (Note the preceding space).
    Value contained in IN_HH = -11
    The result of TO_CHAR(IN_HH, 'fm09') would be ---->
    '-11'

    Hope this will help. Thanx again...............

  6. #6
    Join Date
    Feb 2004
    Posts
    88
    create table #bill (col1 int not null)
    insert #bill values (1)
    insert #bill values (12)


    select replicate('0' , 2 - len(convert(varchar(2), col1))) + convert(varchar(2), col1)
    from #bill

    Bill

  7. #7
    Join Date
    Nov 2004
    Location
    India
    Posts
    31

    Thumbs up

    Thanx Bill, you have been a great help!

    BTW, I also came across this solution ---->

    declare @n int
    set @n=5
    print replace(str(@n,2,0),' ','0')
    -> result 05

    set @n=05
    print replace(str(@n,2,0),' ','0')
    -> result 05

    What are ur comments to this?

  8. #8
    Join Date
    Feb 2004
    Posts
    88
    just as good...

Posting Permissions

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