DECLARE@Sample TABLE

(

RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

Data VARCHAR(5) NOT NULL

)

INSERT@Sample

(

Data

)

SELECT'' UNION ALL

SELECT'1' UNION ALL

SELECT'11'

;WITH cte(Digit)

AS (

SELECT ' ' UNION ALL

SELECT '1'

)

INSERT@Sample

(

Data

)

SELECTp1.Digit + p2.Digit + '.' + p4.Digit + p5.Digit

FROMcte AS p1

CROSS JOINcte AS p2

CROSS JOINcte AS p4

CROSS JOINcte AS p5

ORDER BYp1.Digit,

p2.Digit,

p4.Digit,

p5.Digit

DELETE

FROM@Sample

OUTPUTdeleted.RowID,

deleted.Data

WHEREISNUMERIC(Data) = 0

-- Ordinary convert, fails for RowID 4

SELECTData,

CAST(Data AS DECIMAL(4, 2)) AS ConvertedValue

FROM@Sample

WHERERowID = 6

-- Here is a failsafe conversion

SELECTRowID,

Data,

CAST(CASE PATINDEX('%.%', Data)

WHEN 0 THEN '0' + RTRIM(LTRIM(Data))

ELSE RTRIM(LTRIM(Data)) + '0'

END AS DECIMAL(4, 2)) AS Peso

FROM@Sample