How to convert from string to numeric with trailing sign

• mikelordi2

  Mr or Mrs. 500

  Points: 596

  More actions

  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?

• WayneS

  SSC Guru

  Points: 95461

  More actions

  March 18, 2010 at 9:15 am

  #1136044

  Does this do it for you?

  declare @test varchar(50),

  @num int

  set @test = '000150000-'

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

  select @test, @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

• mikelordi2

  Mr or Mrs. 500

  Points: 596

  More actions

  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

• divyanth

  SSCarpal Tunnel

  Points: 4303

  More actions

  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