Error while converting varchar to Decimal

0
votes

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
3
sqlsql-server
If 2012+, use try_convert() or try_cast(). Rather than throwing an errror, these will return NULL values if the conversion fails. - John Cappelletti
Have you tried selecting your substring to see if you've actually got the right part? - HoneyBadger
yes, I had to put -10 instead. I get the number part correctly now - VBS
I highly recommend fixing your design. Violating the first normal form of database design will cause you nothing but problems. - UnhandledExcepSean

3 Answers

3
votes

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
1
votes

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.

0
votes

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.