How to Add decimal & zero's after number

I got a problem with converting values.

My Source values are:

• 1
• 2
• 3
• 4
• 1 - abcd
• 2 - xyzabcdefgh
• abcdefgh
• lmnop

I need开发者_StackOverflow the output as

• 1.00
• 2.00
• 3.00
• 4.00

The problem I have is that some fields contain letters only, and there are also those that contains contain both letters and digits.

I only need to format those numbers only:

• 1 as 1.00
• 2 as 2.00

The fields containing letters need to remain the same.

Any help please?

Try something like

select case 
    when MyVarcharColumn NOT LIKE '%[^0-9]%' then convert(decimal(18,2), MyVarcharColumn) 
    else MyVarcharColumn 
end as MyVarcharColumn
from x

Might not be the most elegent/efficient solution, but it should work

I also Agree with Joe

You can do what Lee said, or enclose within a try catch

Declare @str varchar(10)


Set @str = '1'
Begin Try
    Select Convert(Decimal(10,2), @str) -- scale 10, precision 2
End Try
Begin Catch
    Select 'Not Numeric'
End Catch

try this

select 
  case when col1 NOT LIKE '%[^0-9]%'
     then convert(varchar(50), convert(decimal(18,2), col1)) 
     else col1 end as col1 from mysource