Varchar to Decimal

VARCHAR to DECIMAL

Implemented using Custom Function.
This will check whether the string value can be converted to Decimal safely

CREATE FUNCTION [dbo].[TryParseAsDecimal]
(
    @Value      NVARCHAR(4000)
    ,@Precision INT
    ,@Scale     INT
)

RETURNS BIT
AS
BEGIN

    IF(ISNUMERIC(@Value) =0) BEGIN
        RETURN CAST(0 AS BIT)
    END
    SELECT @Value = REPLACE(@Value,',','') --Removes the comma

    --This function validates only the first part eg '1234567.8901111111'
    --It validates only the values before the '.' ie '1234567.'
    DECLARE @Index          INT
    DECLARE @Part1Length    INT 
    DECLARE @Part1          VARCHAR(4000)   

    SELECT @Index = CHARINDEX('.', @Value, 0)
    IF (@Index>0) BEGIN
        --If decimal places, extract the left part only and cast it to avoid leading zeros (eg.'0000000001' => '1')
        SELECT @Part1 =LEFT(@Value, @Index-1);
        SELECT @Part1=SUBSTRING(@Part1, PATINDEX('%[^0]%', @Part1+'.'), LEN(@Part1));
        SELECT @Part1Length = LEN(@Part1);
    END
    ELSE BEGIN
        SELECT @Part1 =CAST(@Value AS DECIMAL);
        SELECT @Part1Length= LEN(@Part1)
    END 

    IF (@Part1Length > (@Precision-@Scale)) BEGIN
        RETURN CAST(0 AS BIT)
    END

    RETURN CAST(1 AS BIT)

END

SQL Server: Convert varchar to decimal (with considering exponential notation as well)

You may try following, but you may loose accuracy:

select cast(cast('1.61022e-016' AS float) as DECIMAL(28, 16))

Converting numeric values in varchar to decimal

If you want to use the CASE, then

Query

select 
    case when ISNUMERIC(rtrim(ltrim([PCI1__c]))) = 1
    then convert(decimal(10, 2), PCI1__c)
    else null end as PCI1__c 
from [MX_CREDIT_ANALYSIS];

CONVERT varchar to decimal not working on SQL Server

Try this...

SELECT (COALESCE(CONVERT(decimal(12,2), Lineas.PARAM1), 0.00)) * (COALESCE(CONVERT(decimal(12,2), Lineas.PARAM2), 0.00))  AS 'MULTIPARAM'

the issue is here..

SELECT COALESCE('1', 0.00)

if still you get error "Error converting data type varchar to numeric", then your data has some characters else than 0-9.

Error while converting varchar to Decimal

You could use the LIKE operator in the JOIN for such situations.
But if you really have to get a substring from such string ('abc^1234567^xyz'), you could use such construction:

 SELECT  
        a.Status,
       Cast(SUBSTRING(sourceNatKey, CHARINDEX('^',sourceNatKey) + 1,CHARINDEX('^',sourceNatKey, CHARINDEX('^',sourceNatKey)+1) - CHARINDEX('^',sourceNatKey) - 1)  As decimal(18,4)),
        b.caseId
    FROM AGREEMENT_STATUS a
    INNER JOIN APPLICATION_FACT b
    ON 
    Cast(SUBSTRING(sourceNatKey, CHARINDEX('^',sourceNatKey) + 1,CHARINDEX('^',sourceNatKey, CHARINDEX('^',sourceNatKey)+1) - CHARINDEX('^',sourceNatKey) - 1)  As decimal(18,4)) = 
    b.caseID

Arithmetic overflow error while converting varchar to decimal datatype

You need to define Decimal(16,2) to convert varchar(14) to decimal. You can alternatively use Cast instead of Convert.

Declare @varchar_variable varchar(14), @decimal_variable decimal(16,2)

Set @varchar_variable = '10000000000189'

Select @decimal_variable = CAST(@varchar_variable AS DECIMAL(16,2))

-- Select @decimal_variable = Convert(Decimal(16,2),@varchar_variable)

SELECT @decimal_variable

Converting VARCHAR to DECIMAL values in MySql

I think you need to try doing something like this on your MySQL if you have admin privilege on your MySQL.

ALTER TABLE tablename MODIFY columnname DECIMAL(M,D)

for the M,D variables, read this - http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html

And MySQL should be able to automatically converting a text to a numeric. Just that the data type in MySQL might not be a decimal yet that's why you can't store any decimal.

How to convert a varchar column to decimal?

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

---

Related Topics