len function issue

  • 12 years ago


    I really don't know why, every time I try to execute the following statement:
                    select convert(decimal(10,len(AMOUNT)),AMOUNT) from CALIB_ANALYTES

    I receive an error message: "Line 1: Incorrect syntax near 'len'."

    If instead of Len(AMOUNT) I put a number, it works fine.

    Please advise me.

    Thank you,

  • 12 years ago
    I dont know what db you are using but I suspect that you cannot supply an Expression to decimal's precision and scale parameters. I know you cant with Sybase or SQL Server.


  • 12 years ago
    Yes, I'm using SQL Server.
    Are you very sure that I cannot a function instead of a certain value for parameters in decimal? If this is so, how else could I solve this?

    Thank you,
  • 12 years ago

    Yes it is illegal in SQL Server.

    If you are trying to turn integers into decimals then

           select AMOUNT / Power( 10 , len(AMOUNT) )

    will return .12345 where AMOUNT = 12345

    It really depends on what datatype AMOUNT is and what you are trying to do to it.



  • 12 years ago

    You're right, I'm sorry.

    The AMOUNT field is varchar and contains values like 4.0 or 12.20.
         What I'm actually trying is to format another filed(numeric this time, containing a calculated value based on AMOUNT), from another table to have the same number of decimals like this one. I solved that issue using round instead (not a very good solution, but at list accepts expresions as parametes) and a very complicated nested cases.

    Thanks anyway for your responses

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Never trust a programmer in a suit.” - Anonymous