SQL Server – Handling Strings Representing Negative Numbers

sql serversql-server-2008-r2t-sql

I would like for every string that has a letter on the end make it a negative value.

<field> = '00000000014545p';--Varchar(15)
<field> = '00000000012645x';--Varchar(15)
<field> = '00000000012345p';--Varchar(15)
<field> = '000000004512345';--Varchar(15)

SELECT 
    SUM(CAST(<field> AS int))

FROM <table> WHERE <field> IN (38857,45678)

I want a return the sum of the fields.

Update: Found out that this was programmed using "over punch dibol ascii" This is why the letters are in this. p=0,q=1,r=2,s=3,t=4,u=5,v=6,w=7,x=8,y=9
Letter means *-1

Best Answer

You should really consider fixing this data before it gets into your tables, because working around bad data is cumbersome.

DECLARE @x TABLE(col VARCHAR(15));

INSERT @x(col) VALUES
('00000000014545p'),('00000000012645n'),
('00000000012345p'),('000000004512345');

;WITH x AS 
(
  SELECT col, switch = CASE WHEN RIGHT(col,1) NOT LIKE '[0-9]' THEN 1 END
  FROM @x
)
SELECT SUM(CONVERT(INT, LEFT(col, 
  CASE switch WHEN 1 THEN LEN(col)-1 ELSE LEN(col) END)) 
   * CASE switch WHEN 1 THEN -1 ELSE 1 END)
FROM x
-- WHERE ...;
Related Question