CODEWITHSUNDEEP
sql-server
user2447136
user2447136

Reputation: 189

How to convert a varchar column to decimal?

Here is sample data

-- Script to Create Table Schema

Create table myTable1(
    PREMIUM  varchar(50))

-- Script to Create Desired Data

Insert into myTable1 
values('25.90')
,('36.98')
,('67.89')
,('')
,('')

How do I convert varchar to decimal? I have tried the below but getting error converting varchar to numeric

--script to create table where premium field is converted to decimal
select cast(premium as decimal(12,2)) as prem
into myTable2
from myTable1

Upvotes: 1

Views: 3458

Answers (2)

Atique Rahman
Atique Rahman

Reputation: 151

You can't convert character to Decimal. You can try this for ignoring blank space.

SELECT CASE 
        WHEN PREMIUM = ''
            THEN 0.00
        ELSE CAST(PREMIUM AS DECIMAL(12, 2))
        END AS PREM
INTO myTable2
FROM #myTable1

Upvotes: 1

PSK
PSK

Reputation: 17943

If you want to ignore invalid values and only want to insert valid decimals for that you can try like the following using TRY_PARSE .

select TRY_PARSE (premium as decimal(12,2)) as prem
into myTable2
from myTable1
where TRY_PARSE (premium as decimal(12,2)) IS NOT NULL

or like following using CTE

;WITH CTE AS
(
select TRY_PARSE (premium as decimal(12,2)) as prem
from myTable1
)
SELECT * into myTable2 FROM CTE
where prem IS NOT NULL

You can read more about TRY_PARSE here

Upvotes: 1

Related Questions