Solved

How to exclue numeric data from the select?

Posted on 2011-03-14
7
414 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: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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 51

Expert Comment

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

Assisted Solution

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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