How to convert string to integer - weird format

问题:

I have a field named: pa_value wich keeps varchar records Now this field contains records like:

 - 0.5582%
 - 0.6985%
 - -0.1589%
 - 0.9856%
 - -0.6589%

I'm getting these results using the following code:

CAST (replace (p7.pa_value ,'%','') AS float (3,0)) as TotalMargin

What I'm trying to do is to remove everything and leave just 2 characters(or 3 if there is a -(minus) infront of the string). It should be looking like this:

 - 55
 - 69
 - -15
 - 98
 - -65

I tried to cast it as a float and then to convert it to integer. I also tried the floor command, wich is not for my case, without any success. I belive that there is no way to do this

回答1:

SELECT CAST((columnName * 100) as INTEGER) NewValue
FROM TableName;

• SQLFiddle Demo

回答2:

try this:

select cast(substring(col,0,charindex('.',col)+3) as float)*100 
from table1

SQl fiddle demo

回答3:

This should work:

SELECT 
  CAST( CASE WHEN LEFT(VALUE, 1) = '-' THEN '-' ELSE '' END 
  + SUBSTRING(
        Value, 
        CASE WHEN LEFT(VALUE, 1) = '-' THEN 4 ELSE 3 END,
        2)
  AS INT) AS TotalMargin
FROM TableName

Fiddle-Demo

回答4:

Try this

UPDATE [SomeTable] 
SET p7.pa_value = 
    (CASE 
         WHEN LEN(LTRIM(p7.pa_value)) > 8 
            THEN  CAST(RIGHT(p7.pa_value, LEN(p7.pa_value) - 2) AS NUMERIC(10, 2))
         ELSE CAST(LTRIM(p7.pa_value) AS NUMERIC(10, 2))
     END);
GO

This checks whether the string has the leading unwanted '-' and deals with each case accordingly.

I hope this helps.