Characters in LINQ and SQL

Question

0.00/5 (No votes)

See more:

I try this sp and linq query
SP
select distinct Region from tblRe where Region
this query return

SQL

ISL
Paris
NY
SDF-23
LON
SDF-43
234
AM
RU
KAS
FJS-35
SDF-32
SDF-45

i DONT want value which have numbers/characters..
how i do that in sql

and then i try this in linq

What I have tried:

SQL

var list = tea.tblRe.AsEnumerable()
                       .Where(x => !x.Region.Any(char.IsDigit))
                        .ToList().Distinct();

this show only names but with repetition ie.g

SQL

ISL
Paris
NY
NY
LON
KAS
AM
RU
KAS
LON
LON
AM
KAS
KAS

now i don't want repetition

any solu?

Posted 26-Jul-16 23:46pm

super_user

Updated 27-Jul-16 2:17am

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2016-07-27T02:17:00

Solution 3

To do this in SQL, you need to use a NOT LIKE comparison:

SQL

SELECT DISTINCT
    Region 
FROM
    tblRe 
WHERE
    Region Not Like '%[0-9]%'
;

LIKE (Transact-SQL)[^]

Posted 27-Jul-16 2:17am

Richard Deeming

Tomas Takac · Accepted Answer · 2016-07-27T02:12:00

Solution 2

The problem is that you are running distinct not on the region property but on the whole record which is a complex object. Try this:

C#

var list = tea.tblRe.AsEnumerable()
    .Select(x => x.Region)
    .Where(region => !region.Any(char.IsDigit))
    .Distinct()
    .ToList();

Posted 27-Jul-16 2:12am

Tomas Takac

Comments

[no name] 27-Jul-16 8:20am

A 5, just learnd from your answer :)

Karthik_Mahalingam 27-Jul-16 10:17am

5, best one.

OriginalGriff · Accepted Answer · 2016-07-27T01:26:00

Solution 1

Remove the ToList - it does nothing to help - and check your data.
When I simulate your code here:

C#

List<string> list = new List<string>();
list.Add("ISL");
list.Add("Paris");
list.Add("NY");
list.Add("SDF-23");
list.Add("LON");
list.Add("SDF-43");
list.Add("234");
list.Add("AM");
list.Add("RU");
list.Add("KAS");
list.Add("FJS-35");
list.Add("SDF-32");
list.Add("SDF-45");
var list2 = list.AsEnumerable()
           .Where(x => !x.Any(char.IsDigit))
           .Distinct();</string></string>

I get exactly what I expect:

C#

ISL
Paris
NY
LON
AM
RU
KAS

Without any duplications.

Posted 27-Jul-16 1:26am

OriginalGriff

Comments

[no name] 27-Jul-16 8:07am

Sorry, again me with a question. I also tested and everything looks ok. Now assume the raw data has a "KAS" and a "KAS " in it. Is there any way to Trim "x" before adding it to the result list? I tried x.Trim().Any(..., but it does not Trim (of course?) the string from the source list.
Thank you very much in advance.

[Solved] by the answer #2 of Tomas Takac :)

OriginalGriff 27-Jul-16 8:22am

Yes - Select:
.Where(x => !x.Any(char.IsDigit))
.Select(x => x.Trim())
.Distinct();