CODEWITHSUNDEEP
c#sqllinqentity-framework
amit325
amit325

Reputation: 111

In a table a column datatype is varchar, retrieve only those values of the column which are number / numeric using linq to sql

I have a table with a column of datatype varchar. I need to retrieve only those values from the column which are pure numbers. For this I used the Linq query shown below, which only checks for lower case alphabets. It won't able to check for this @-1234. I am providing the details what exactly I want to do.

   | input |     | expected output |
   | @!qw1 |     |      12345      |
   | 12345 |     |      90345      |
   | ab567 |     
   | 90345 |             
   | 123-q |

Code:

 pricingdemoEntities price = new pricingdemoEntities();

 var money = (from demos in price.demotables
              where false == (demos.value.Contains("a") || demos.value.Contains("b") || demos.value.Contains("c") || demos.value.Contains("d") || demos.value.Contains("e") || demos.value.Contains("f") || demos.value.Contains("g") || demos.value.Contains("h") ||
                              demos.value.Contains("i") || demos.value.Contains("j") || demos.value.Contains("k") || demos.value.Contains("l") ||
                              demos.value.Contains("m") || demos.value.Contains("n") || demos.value.Contains("o") || demos.value.Contains("p") || 
                              demos.value.Contains("q") || demos.value.Contains("r") || demos.value.Contains("s") || demos.value.Contains("t") || 
                              demos.value.Contains("u") || demos.value.Contains("v") || demos.value.Contains("w") || demos.value.Contains("x") ||
                              demos.value.Contains("y") || demos.value.Contains("z")) 
              select demos.value).ToList();

foreach (var final in money)

Upvotes: 2

Views: 536

Answers (6)

Peter Hansen
Peter Hansen

Reputation: 8907

Instead of specifying every single character that makes the value not a number, it's much easier to just specify that you only want the value if it's a number.

So in raw SQL that could look something like this:

SELECT val
FROM table
WHERE ISNUMERIC(val) = 1

In Entity Framework you can create a similar query by using the handy SqlFunctions.IsNumeric method.

In your case it would look something like this:

var money = (
    from demos in price.demotables
    where SqlFunctions.IsNumeric(demos.value) == 1
    select demos.value).ToList();

Upvotes: 0

Mahdi Farhani
Mahdi Farhani

Reputation: 1012

try use this 

price.demotables.Where(x=>SqlFunctions.IsNumeric(x.Data)==1).ToList()

Upvotes: 0

reckface
reckface

Reputation: 5868

These queries cannot be translated to SQL by Entity Framework, so one option is to materialise your results first. This isn't very efficient, but you can improve it by limiting what you bring back in your query. And then perform the numeric check on the results.

For example:

var acceptable = ".,0,1,2,3,4,5,6,7,8,9"; // list of acceptable values
var nums = acceptable.Split(',').Select(a=> a[0]); // get the chars
using(var price = new pricingdemoEntities())
{
  // You can limit this even further by adding a take or additional where clauses
  var query = price.demotables.Select(p => p.value).AsEnumerable();
  var money = query.Where(q=> q.value.All(c=> nums.Contains(c)).ToList();
}

Upvotes: 0

Yuliam Chandra
Yuliam Chandra

Reputation: 14640

You can use SqlFunctions.IsNumeric extension method.

using System.Data.Entity.SqlServer;

var money = (from demos in price.demotables
    where SqlFunctions.IsNumeric(demos.value) == 1
    select demos.value).ToList();

Upvotes: 4

Hitesh
Hitesh

Reputation: 3880

Please see below example, may be this will help you out.

string[] stringList = { "123", "xsd", "%Vhh", "123H", "5841" }; // just an array of strings

var a = stringList.Where(x => x.All(char.IsDigit)); // checking for digits and returning them

I hope this will help you :)

Upvotes: 0

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can use long.TryParse to check for the numeric value

pricingdemoEntities price = new pricingdemoEntities();
long num;
var money= (from demos in price.demotables

        where long.TryParse(demos.value.ToString(),out num) 
        select demos.value).ToList();
foreach (var final in money)

Upvotes: 0

Related Questions