Solved

How to exclue numeric data from the select?

Posted on 2011-03-14
7
424 Views
Last Modified: 2012-05-11
There are some errornous data entered in form of 4-digit year.  How can I exclude inserting these?  Thanks.
INSERT INTO [DegreeMajor]
           ([Major Title])
           select distinct ltrim(rtrim(degree_Major)) from dbo.PersonDegree pd
                  where degree_Major is not null and degree_Major not in ('MD', 'Master', 'B.A.', 'M.D.', '.', '', 'Doctor', 'MSC')
0
Comment
Question by:lapucca
7 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 300 total points
ID: 35132649
Try ISNUMERIC for SQL:

select distinct ltrim(rtrim(degree_Major)) from dbo.PersonDegree pd
where degree_Major is not null and ISNUMERIC(degree_Major)=1
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 35132670
select distinct ltrim(rtrim(degree_Major))
from dbo.PersonDegree pd
where ISNUMERIC(degree_Major)=1

is enough...
0
 

Author Comment

by:lapucca
ID: 35132691
I don't want numeric value so should it be ISNUMERIC(degree_Major)=0?  Thanks.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 35132748
!= 1
0
 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 200 total points
ID: 35132754
ISNUMERIC(degree_Major)=0

or

ISNUMERIC(degree_Major)!=1
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35132755
Yes if you are looking for values that are not numeric it should be

ISNUMERIC(degree_Major)=0

or

ISNUMERIC(degree_Major) <> 1
0
 

Author Closing Comment

by:lapucca
ID: 35133777
Thank you.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question