CODEWITHSUNDEEP
sqlsql-server
VBS
VBS

Reputation: 77

Error while converting varchar to Decimal

In my table I have a column(Varchar). It contains values like 'abc^1234567^xyz'. I need to select part of the value and convert it to select the number and convert it to a decimal number and compare this number to a number from another table by joining two tables. So far I get error while want to convert it from varchar to decimal.

Here is my error message: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. Here is my code:

SELECT  
    a.Status,
    Cast(SUBSTRING(sourceNatKey, LEN(sourceNatKey)-11,7) As Decimal(18,4)),
    b.caseId
FROM AGREEMENT_STATUS a
INNER JOIN APPLICATION_FACT b
ON 
Cast(SUBSTRING(sourceNatKey, LEN(sourceNatKey)-11,7) As decimal(18,4)) = 
b.caseId

Upvotes: 0

Views: 797

Answers (3)

Herman
Herman

Reputation: 56

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You could do this the other way:

SELECT  
    a.Status,
    b.caseId
FROM AGREEMENT_STATUS a JOIN
     APPLICATION_FACT af
     ON sourceNatKey LIKE '%^' + CAST(b.caseID as varchar(255)) + '^%'

The caseId column has the information in your second column, so that doesn't need to be repeated.

Upvotes: 1

Joey
Joey

Reputation: 670

The code SUBSTRING(sourceNatKey, LEN(sourceNatKey)-11,7) will result in ^123456 given the value abc^1234567^xyz. This fails because ^ is non-numeric. Change the offset value from the LEN call to be -10 in order to only retrieve the numeric part of your string.

For further information regarding SUBSTRING, see SUBSTRING (Transact-SQL) Documentation.

Upvotes: 0

Related Questions