There is by default function in SQL Server ISNUMERIC() so, first of all Check your data value by that function,

Select ISNUMERIC(DATA)

Whole query is written as below,

SELECT CASE WHEN ISNUMERIC(data)=1 THEN CAST(data as decimal(18,2))
            ELSE NULL END as tData FROM DataTable

As per your question,first we have to convert with numeric with using case,which satisfies your first condition,another thing if the value is String than convert as NULL . In Above query both the condition has been taken care.

EDIT : If you are using SQL SERVER 2012 or higher version then use TRY_PARSE() , then there will be no need to worry about using CASE too...

I have tried this,

SELECT TRY_PARSE('63.36' as decimal(18,2)) got result 63.36

and

SELECT TRY_PARSE('.' as decimal(18,2)) got result NULL

I think that this fits your spec. It is quite verbose, but hopefully it breaks down the conditions sufficiently that it's clearly doing the correct thing or, if it isn't, that it's easy enough to modify:

declare @t table (data varchar(30))

insert into @t(data) values
('1234'),
('1.23'),
('abc'),
('.abc'),
('+6000'),
('1.2.3')

select
    CASE WHEN
        Possible = 1 AND
            (DecCheck = 0 OR
            SingleDec = 1
        ) THEN
            CONVERT(decimal(12,3),data)
        END
from
    @t t
        cross apply
            (select
                --Only contains the correct characters
                CASE WHEN not t.data like '%[^0-9.]%' THEN 1 ELSE 0 END as Possible,
                --Contains a decimal point? (Needs more checks)
                CASE WHEN CHARINDEX('.',t.data) > 0 THEN 1 ELSE 0 END as DecCheck,
                CHARINDEX('.',t.data) as FirstDec --Where the first decimal point is
            ) p
        cross apply
            (select
                CASE WHEN DecCheck = 1 THEN
                    --Only contains one decimal point
                    CASE WHEN LEN(data) = FirstDec + CHARINDEX('.',REVERSE(data)) - 1
                        THEN 1
                    ELSE 0 END
                ELSE 0 END as SingleDec
            ) d

Results:

data                           
------------------------------ ---------------------------------------
1234                           1234.000
1.23                           1.230
abc                            NULL
.abc                           NULL
+6000                          NULL
1.2.3                          NULL

I.e. one additional check you may want to use is that a decimal cannot be the first or last character in the string. That is easy enough to do by adding those additional checks into the first CASE for the SingleDec column.

try ISNUMERIC function,

SELECT ISNUMERIC('abc')
SELECT ISNUMERIC('1.23')

On SQL Server (Version 2012, 11.0.5343)

SELECT
    CASE WHEN ISNUMERIC('.') = 1 THEN <Field> ELSE 0 END
FROM
    <Table>

works fine ...

Thre is A blog post .

Try following

SELECT 
    CASE 
        WHEN 
            ISNUMERIC(data + 'e0') = 1 THEN CAST(data AS decimal(18,2))
        ELSE NULL END AS tData 
FROM 
    DataTable

try ISNUMERIC function

DECLARE @MyTable TABLE(Val VARCHAR(100))

INSERT INTO @MyTable
VALUES
     ('1234')
    ,('1.23')
    ,('ABC')
    ,('.ABC')
    ,('MJA')

Select  Val as OldValue,
        Case
        When ISNUMERIC(Val) = 1
        then Cast(Val as numeric(18,2))
        else null
        end NewValue
From    @MyTable

Output

OldValue                                  NewValue
-----------------------------------------------------
  1234                                      1234.00
  1.23                                      1.23
  ABC                                       NULL
  .ABC                                      NULL
  MJA                                       NULL

  (5 row(s) affected)