sqlsql-serversql-convert

Sql Server using Convert and Replace together in the same statement

I wanted to double check my logic for a query in SQL Server.

The idea is that I am able to feed the following values and it will make sure the result is a decimal with four trailing digits.

Possible values for @LABORQTY:

1,200

1,200.42

1200 (Integer)

1200.42

1200 (As a String)

1200.42 (As a String)

When the value is a string, it will give the error: Error converting data type nvarchar to numeric.

Here is my code:

CONVERT(DECIMAL(12, 4), REPLACE(@LABORQTY, ',', ''))

The output each time though should be decimal: 1200.4200

Solution

  • Your question is really confused, but I'll answer according to the following parameters:

    @laborqty is a VARCHAR

    @laborqty may somehow come to contain any of the following values:

    '1200'
'1200.42'
'1,200'
'1,200.42'

    In which case CONVERT(DECIMAL(12, 4), REPLACE(@LABORQTY, ',', '')) will indeed produce a decimal with up to 4 digits of fractional precision. Whether your query tool/programming language will output it as 1200.4200 or not is another matter entirely; it might well just output 1200.42 and drop the trailing zeroes

    If you're getting Error converting data type varchar to numeric. still, there is some other character data (not comma) in your numeric string

    If you definitely want the trailing zeroes, format it into a string before you output

    FORMAT(CONVERT(decimal(12,4), '1200.42'), '0.0000')

    This will generate a string with 4 trailing zeroes