0

I have a column that holds VARCHARs and I have a flag that determines if the value is a string or integer. I would like perform a search on the integers but they are stored as VARCHAR. How do I do this?

I am trying to do this in a where clause. 

var q = from x in context.table
        where x.Criteria > 0
        select new
        {
              x
        }
Luke101
  • 63,072
  • 85
  • 231
  • 359
  • 5
    having "a flag that determines if the value is a string or integer" like that often indicates there is a problem with your design... – Mitch Wheat Aug 22 '10 at 04:26
  • @Mitch Wheat: No, not really. – Timwi Aug 22 '10 at 04:35
  • @Timwi: Yes, really (if you are using a single column/field to store different types). – Mitch Wheat Aug 22 '10 at 04:40
  • 1
    @Mitch Wheat: OMG, how could I be so wrong. Your astute argumentation is utterly convincing! – Timwi Aug 22 '10 at 04:42
  • Perhaps he's emulating the Windows Registry, which needs `INTEGER`, `TEXT`, and `BLOB` values in the same column. – dan04 Aug 22 '10 at 04:51
  • 3
    @Timwi: that's a strange tone to take. If you are storing different types in a column, it is 'often' (note the word 'often') an indication that the design is not quite right. – Mitch Wheat Aug 22 '10 at 04:57
  • @dan04: the mention of 'VARCHARs ' would suggest he is not enumerating the registry...plus the tag of "SQl Server 2008" – Mitch Wheat Aug 22 '10 at 04:58
  • 1
    @Mitch Wheat: The relational nature of SQL databases imposes limitations on the structure the data can have, including for example the conspicuous absence of polymorphism. There are numerous real-life business use-cases that mandate a minimum of type variability within collections of otherwise related entities and/or a minimum of run-time (post-implementation) customisability. A significant proportion of such use-cases do not map trivially to relational database tables, necessitating a model, e.g. the key/value pair pattern, in which values need to be of different types within the same column. – Timwi Aug 22 '10 at 05:38
  • 4
    @Timwi: I'm aware of those facts, thanks. If "A significant proportion of such use-cases do not map trivially to relational database tables" then don't use a relational database. – Mitch Wheat Aug 22 '10 at 05:44
  • If you are using key value tables, likely you have a very bad design from both a performance and maintenance perspecitve. – HLGEM Aug 23 '10 at 16:54

3 Answers3

1

CAST(TheColumn AS INTEGER)

dan04
  • 87,747
  • 23
  • 163
  • 198
1

sql

select isnull(Convert(int, varcharColumn),0)
from atable
where isInteger = 1

linq

var q = context.YourCustomView;
Holystream
  • 962
  • 6
  • 12
1

You'll probably need to pull down the values from the server as strings and do the conversion in LINQ to Objects.

var q = context.table
    .Where(x => x.Criteria > 0)
    .Select(x => x.IntOrStringValue)
    .AsEnumerable()
    .Select(v => Convert.ToInt32(v));

replace x.Criteria > 0 with your logic that indicates the value is an integer. replace x.IntOrStringValue with your column containing the integers.

GWB
  • 2,575
  • 2
  • 23
  • 28