How to convert a Excel format number to numeric(decimal or float)?

Question

I have a nvarchar(255) column which store numbers imported from excel long time ago.

Format like below:

7,242
(4,011)
-
(24,120.2)
-
4,234,091

"-" means 0 here.

I'm currently using "replace" to make it converted to decimal

convert(decimal(18,4),RTRIM(LTRIM(replace(replace(replace(replace([Column a],'-',0),',',''),'(','-'),')',''))))

Anyhow, I think it is a little bit stupid. Any better way?

Are we speaking about mssql (T-SQL)? convert(money,'1,234,567.8',1) converts formatted string nice; convert(money,'-',1) converts to 0 you can convert the result to any numeric type — vitalygolub, Aug 15 '16 at 09:31

score 0 · Answer 1 · edited May 23 '17 at 12:08

You could use a modified version of the function from Query to get only numbers from a string to handle your Excel values.

CREATE FUNCTION dbo.udf_GetNumeric
    (
     @strAlphaNumeric VARCHAR(256)
    )
RETURNS VARCHAR(256)
AS 
    BEGIN
       DECLARE @intAlpha INT
       SET @intAlpha = PATINDEX('%[^0-9.-]%', @strAlphaNumeric)
       BEGIN
          WHILE @intAlpha > 0 
             BEGIN
                SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha,
                                        1, '')
                SET @intAlpha = PATINDEX('%[^0-9.-]%', @strAlphaNumeric)
             END
       END       
       RETURN CASE WHEN ISNULL(@strAlphaNumeric,0)='-' THEN 0 ELSE CONVERT(DECIMAL(18,4),@strAlphaNumeric) END 
    END
GO

You can then convert your values as follows:

SELECT dbo.udf_GetNumeric('7,242')
SELECT dbo.udf_GetNumeric('(4,011)')
SELECT dbo.udf_GetNumeric('-')
SELECT dbo.udf_GetNumeric('(24,120.2)')
SELECT dbo.udf_GetNumeric('-')
SELECT dbo.udf_GetNumeric('4,234,091')

The implementation is not necessarily better, but it makes it far easier to understand what the code is trying to achieve.

How to convert a Excel format number to numeric(decimal or float)?

1 Answers1