How to simulate a field that can contain both numeric and string values ​​in SQL Server 2005?

Question

I have a new database table that I need to create ...
It logically contains ID, a nameand a "value".
This value field can be either a numeric or a character string in nature.

I don’t think I just want to make the field a varchar, because I also want to be able to request filters such as WHERE value > 0.5etc.

What is the best way to model this concept in SQL Server 2005?

EDIT: I am not opposed to creating several fields here (one for numbers, one for non-numbers), but since they are all really the same concept, I was not sure if this is a great idea.
I think I could create separate fields and then have a view that combines them into one logical column.

Any opinions on this?

What I want to achieve is really quite simple ... usually this data will simply be displayed blindly in a grid.
I also want to be able to filter the numerical values ​​in this grid. This table will hit tens of millions of records, so I don’t want to draw myself in the corner with a performance request.
This important task is my main problem.

Answer 1

, Sql 2005 . "" .

varchar, :

where value > '20.5'

"5" ( "5" "20.5" )

.

Coalesce, , , :

select [ID], [Name], Coalesce( [value_str], [value_num] )
from [tablename]

Answer 2

- : numvalue, , . NULL , , , . , .

Answer 3

, , .

Answer 4

.

Table: (ValueLable as char(x), Value as numerica(p,s))

Answer 5

, varchar int type. varchar int . , . ?

Answer 6

, , , varchar .

2 3 . , : value_type ( "" "" ), number_value, string_value.

WHERE value_type = 'number' AND number_value > 0.5

Answer 7

, , VARCHAR NVARCHAR . , , , CAST CONVERT .

Answer 8

, , , . ?

. ( - ) - , , (varchar ..), , , .

SELECT ; . ( ), , . , , , , .

"" . , "", , . : , ; .

, "", UPDATE ( , ), , , - .

Answer 9

, , "" "" ( ) "" NOT NULL "" , NULL. "" , , , , "". ( "" , , ), "" "" / . , . , INSERT UPDATE.