?
Solved

How to exclue numeric data from the select?

Posted on 2011-03-14
7
Medium Priority
?
437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 1200 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 58

Expert Comment

by:HainKurt
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 58

Expert Comment

by:HainKurt
ID: 35132748
!= 1
0
 
LVL 58

Assisted Solution

by:HainKurt
HainKurt earned 800 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

762 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