Post reply

How to convert from string to numeric with trailing sign

  • March 18, 2010 at 8:33 am

    #221929

    Hi guys...I am converting data from a flat file to a Sybase table with an assigned datatype of "Long integer". I am deriving the data from a flat file source that passes the string to me as such "000150000-" or "000150000+". I need to convert this string from that to a numeric value of either -1500 or 1500 depending on the sign and dividing by 100.

    I know i have to use a derived table to perform the transformation but am unsure how to do it on one line....anyone have any ideas?

  • March 18, 2010 at 9:15 am

    #1136044

    Does this do it for you?

    declare @test-2 varchar(50),

    @num int

    set @test-2 = '000150000-'

    set @num = left(@test,DataLength(@test)-1) * case when right(@test,1) = '-' then -1 else 1 end

    select @test-2, @num

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 18, 2010 at 9:22 am

    #1136054

    Thanks Wayne, I appreciate it, but I am doing this in a derived column function (along with many other conversions of types). I've found that I can take the data in the column and convert it to an integer by having the output of the flat file on that field set to DT_Decimal and then converting using this:

    [Field]/100

    This passes the value through with the sign, however it is now rounding, so I need to figure out how to pass through the decimals and the sign

  • March 18, 2010 at 9:52 am

    #1136091

    This passes the value through with the sign, however it is now rounding, so I need to figure out how to pass through the decimals and the sign

    This might help

    Bring it in as is (string) and then you can use a derived column to convert it

    RIGHT(Id,1) == "-" ? ((DT_I8)("-" + SUBSTRING(Id,1,LEN(Id) - 1))) / 100 : ((DT_I8)(SUBSTRING(Id,1,LEN(Id) - 1))) / 100

    Note:Id is the column Name

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply