Fail Safe Convert SQL Server Varchar To Float Function
Top : General Technical Support : Programming
| Article ID: |
 |
000041 |
| Rating: |
|
5.0 / 5.0 (1 votes)
|
| Views: |
|
3458 |
|
|
Fail Safe Convert SQL Server Varchar To Float User Function
|
User Function:
CREATE FUNCTION [dbo].[ConvertVarcharToFloat] (@str varchar(128))
RETURNS Float AS
BEGIN
Return (Cast(
Case When patindex('%[^0-9.]%', @str) > 0
Then substring(@str, 1, patindex('%[^0-9.]%', @str) - 1)
Else @str
End
As Float))
END
Test SQL Statement:
Select layer, Cast(Case When patindex('%[^0-9.]%', layer) > 0 Then substring(layer, 1, patindex('%[^0-9.]%', layer) - 1) Else layer End As Float)
From tblTest
Order By dbo.ConvertVarcharToFloat(layer)
Output:
1.0 1.0
1.2 1.2
1.3 1.3
5.51 5.5099999999999998
9.41 9.4100000000000001
9.51kl 9.5099999999999998
15.51 15.51
15.51h 15.51
|
