SQL Syntax: Casting a datatype that is stored as data

Question

Hi,

I have a table that looks like this:

id    datatype        name    value
0     nvarchar(255)   myName  'Paul'
1     int             age     '30'
2     float(53)       Custom1  0.5
3     float(53)       Custom2  1.3
4     float(53)       Custom3  2.7

I am wondering if it is possible to do something like the following where I cast the order by as a float - I know this is incorrect syntax but wondering if this can be done.

SELECT datatype, name, value FROM myTable
ORDER BY (float)name

Thanks in advanced.

Answer 1

To convert datatypes in SQL you can use CAST or CONVERT:

SELECT CAST('123' AS FLOAT), CONVERT(FLOAT, '123')

But if your name field contains a value that cannot be converted to a valid float, you'll get an error.

e.g.

SELECT CAST('NotAValidFloat' AS FLOAT)

will give you:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.