Search code examples
sqllayoutsyntax

sql server logic to workout the right most position of the numeric field will have a sign OVER it designating positive or negative

Example of the data in csv

Column_header

000000025000{

000000007185E

The doucmention I have

*The right most position of the numeric field will have a sign OVER it designating positive or negative.

enter image description here Example of Data

Text

I dont understand how write the logic to support the the symbol,number,letter to get the correct value.

Solution

  • I'd create a table (or view) with the static mapping of character-value, meaning:

    Symbol Value
    J -1
    A +1

    about the data rows themselves, it seems to me there is always a symbol at the end, therefore you can split the data into two columns, value, and symbol...

    I have no idea about how the data are inserted but it seems logically easy

    SELECT
   _YourValue_
  ,LEFT(_YourValue_, LENGTH(_YourValue_)-1) as Value
  ,RIGHT(_YourValue_, 1) as Symbol
FROM _Whatever_

    you can also cast to whatever datatype is correct for those data. Finally you can join the tables and show/calculate whatever is needed