How to convert string to integer - weird format

Question

I have a field named: pa_value which 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, which is not for my case, without any success. I believe that there is no way to do this.

What can you say about the number of decimal places? Is this constant? — MoonKnight
– MoonKnight, Commented Nov 14, 2012 at 9:45

John Woo · Accepted Answer · 2012-11-14 09:40:23Z

3

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

SQLFiddle Demo

answered Nov 14, 2012 at 9:40

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Slim Over a year ago

Here is what I got: Syntax error converting the varchar value '-0.0063' to a column of data type int. :(

John Woo Over a year ago

@Slim Here, try it again (added CAST) Hope it worked now. :D

Colin 't Hart Over a year ago

Do you have NLS settings which expect a comma instead of a period? Then you can use to_number with an explict format model instead.

Slim Over a year ago

My case is kinda specific as the content also contains % in it. Here is what i tried without success CAST((CAST(replace (p7.pa_value ,'%','') AS NUMERIC(10,6)) * 100) as INTEGER) NewValue,

Slim Over a year ago

Hey it worked! Huge thanks!!! I lost about 3 hours looking for a solution!Thanks again!

|

Joe G Joseph · Accepted Answer · 2012-11-14 09:44:12Z

1

try this:

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

SQl fiddle demo

answered Nov 14, 2012 at 9:44

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Comments

Tim Schmelter · Accepted Answer · 2012-11-14 09:45:49Z

1

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

answered Nov 14, 2012 at 9:45

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Comments

MoonKnight · Accepted Answer · 2012-11-14 09:56:40Z

0

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.

answered Nov 14, 2012 at 9:56

MoonKnight

23.9k42 gold badges154 silver badges284 bronze badges

Collectives™ on Stack Overflow

How to convert string to integer - weird format

4 Answers 4

6 Comments

SQl fiddle demo

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related