Solved

How to exclue numeric data from the select?

Posted on 2011-03-14
7
432 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 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 55

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 55

Expert Comment

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

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

705 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