CODEWITHSUNDEEP
sqlsql-server
Gerardo
Gerardo

Reputation: 369

Exclude character to convert into decimal

I have an insert like this 

INSERT INTO (keyMolde, nEficiencia, nCavidades )
VALUES (Origen.keyMolde, Origen.nEficiencia, 
        CASE WHEN CAST(Origen.sCavidades AS INT) = Origen.sCavidades 
                THEN Origen.sCavidades  
                ELSE CAST(Origen.sCavidades AS Numeric(12, 0)) 
        END);

Problem is into Origen.nEficiencia, I'm getting values like: 290% or 91% and I need to convert it to 91.0 or 290.0

How can I convert it? Regards

Upvotes: 0

Views: 40

Answers (1)

McNets
McNets

Reputation: 10817

You can use REPLACE function

REPLACE (Origen.nEficiencia, '%', '.0')

INSERT (keyMolde, nEficiencia, nCavidades )
VALUES (Origen.keyMolde, 
        CAST(REPLACE (Origen.nEficiencia, '%', '.0') AS decimal(18,2)), 
        CASE WHEN CAST(Origen.sCavidades AS INT) = Origen.sCavidades 
             THEN Origen.sCavidades  
             ELSE CAST(Origen.sCavidades AS Numeric(12,0)) 
        END);

Update 

create table test(val varchar(20));
insert into test values
('90%'),('91%'),('23% AUMA');
GO

SELECT CAST(REPLACE(REPLACE(val, 'AUMA', ''), '%', '.0') AS decimal(18,2))
FROM   test;
GO

| (No column name) |
| :--------------- |
| 90.00            |
| 91.00            |
| 23.00            |

dbfiddle here

Upvotes: 1

Related Questions