SQL Server column of type varchar has dot and dash - on right side convert into Decimal-CodePudding

I have a string column which sample data like this:

14.377.616
29.264.814
16.892.140
6.410.850-

and I want output to be like this:

14377616.0000
29264814.0000
16892140.0000
-6410850.0000

The column must be converted to decimal. I have a query like this:

CASE 
    WHEN Column LIKE '%-%' 
        THEN REPLACE(REPLACE(Column, '-', ''), '.', '')
    WHEN Column LIKE '%-%' 
        THEN Column * -1
    ELSE CONVERT(decimal(20, 4), REPLACE([Column], '.', ''))
END

but result the negative cannot display dash in LEFT (but dash is gone) like this:

14377616.0000
29264814.0000
16892140.0000
6410850.0000

Thanks in advance

CodePudding user response：

I would do in this way:

with CastedTable (MyColumn)
as
(
  select replace([Column],'.','') DecimalValue
  from MyTable
)
select 
  case 
  when MyColumn like '%-%' then cast('-'   replace(MyColumn, '-', '') as decimal(20, 4))
  else cast(MyColumn as decimal(20, 4)) end
from CastedTable

Results:


14377616
29264814
16892140
-6410850

SQL Fiddle: http://sqlfiddle.com/#!18/decccb/8

That said, if you want to use your query, it would be this:

select  CASE 
  WHEN [Column] like '%-%' THEN CONVERT(decimal(20,4),REPLACE(REPLACE([Column],'-',''),'.','')) * -1
  ELSE CONVERT(decimal(20,4),REPLACE([Column],'.','')) END AS [Column]

CodePudding user response：

you can use right() to get or - and replace dots using replace() like this

note: '13123.123123' -> colname

select 
 CASE WHEN  RIGHT ( '13123.123123' , 1) <> '-' THEN
 CAST ( REPLACE('13123.123123','.','') AS decimal)
 ELSE
  CAST ( REPLACE('13123.123123','.','') AS decimal) * -1
  END