Home » Microsoft TechnologiesRSS

SQL IsNumeric and Between in Linq

I have a where clause something like this:

and isnumeric(c.class_code) = 1
and c.CLASS_CODE not between '001' and '099'
and c.CLASS_CODE not between '100' and '199'
and c.CLASS_CODE not between '300' and '399'
...

Class code is a char(3). I have been unable to find a good example that shows how to replace the IsNumber or do a BETWEEN in Linq.

Any help is greatly appreciated.

 

 

6 Answers Found

 

Answer 1

 c.class_code.isnumber&&!(c.class_code>1||c.class_code.getnumericvalue<99)&&!(c.class_code>100||c.class_code.getnumericvalue<199)&&!(c.class_code>300||c.class_code.getnumericvalue<399)

Regards

 

Answer 2

I found that since CLASS_CODE is a char(3) that I need to use 

!(tsc.CLASS_CODE.CompareTo("001") >= 0 && tsc.CLASS_CODE.CompareTo("099") <= 0) &&
!(tsc.CLASS_CODE.CompareTo(
"100") >= 0 && tsc.CLASS_CODE.CompareTo("199") <= 0)

IsNumber is not available for string.

 

Answer 3

I found that since CLASS_CODE is a char(3) that I need to use 

!(tsc.CLASS_CODE.CompareTo("001") >= 0 && tsc.CLASS_CODE.CompareTo("099") <= 0) &&
!(tsc.CLASS_CODE.CompareTo(
"100") >= 0 && tsc.CLASS_CODE.CompareTo("199") <= 0)

IsNumber is not available for string.


Sorry, I forgot about the (3) part and only read the char part so yes isnumeric  is for char only, if the string is a number you can check with int.TryParse

CompareTo will compare string but will not ensure that both compared string are numbers so you can compare "001" with "bear" and for example "01" <"001"  because they are compared as strings not as numbers

Regards

 

Answer 4

That is why I also want to check to see if the CLASS_CODE is numeric. I tried:

int i;

 

 

where
(
int.TryParse(tsc.CLASS_CODE, out i) == true) &&
!(tsc.CLASS_CODE.CompareTo(
"001") >= 0 && tsc.CLASS_CODE.CompareTo("099") <= 0) &&
!(tsc.CLASS_CODE.CompareTo(
"100") >= 0 && tsc.CLASS_CODE.CompareTo("199") <= 0) &&

But I get "NotSupportedException: Method 'Boolean TryParse(System.String, Int32 ByRef)' has no supported translation to SQL."

 

Answer 5

Well there is int.Parse and ConvertTo.Int32

Also try to break the statements in smaller parts because sometimes going too deep will confuse Visual Studio

http://msdn.microsoft.com/en-us/library/sf1aw27b.aspx

However you need to put a try catch there because it will throw an exception if the string is not a number

If possible it would be better to change the design of the database to hold numbers in that column not strings.

Regards

 

Answer 6

What I found that worked for me is:

 

where
SqlMethods.Like(tsc.CLASS_CODE,
"[0-9][0-9][0-9]") &&
!(tsc.CLASS_CODE.CompareTo(
"001") >= 0 && tsc.CLASS_CODE.CompareTo("099") <= 0) &&
!(tsc.CLASS_CODE.CompareTo(
"100") >= 0 && tsc.CLASS_CODE.CompareTo("199") <= 0) &&

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter