Solved

IS NUMBER

Posted on 2002-06-26
10
234 Views
Last Modified: 2008-03-06
ok i have a varchar field that i am trying to push into another table.

i have text characters; and when i see these; i would like to filter these (out)--

right now i am using this where clause
'WHERE BUDCSI NOT LIKE '' AND BUDCSI NOT LIKE '' AND BUDCSI NOT LIKE '' AND BUDCSI NOT LIKE ''

and i want a better way to do this.

in access; i would have said 'where IsNumeric(BUDCSI) = TRUE'


INSERT INTO RASB.dbo.tblBudget
                      (BUDID, Prid, CCID, BUDCBI, BUDCSI, BudCO, BUDName, BudValue, BUDLab, BudBurden, BUDMat, BUDSub, BUDVDID, Submittal, SubmitalTitle,
                      SubmitBy, SubmitCSI, ContractORpo, BudCoid, BudMatAdj, BudLabAdj, BudSubAdj, BudAdjId, BudCTD, BudCTC, AccountStatus, BudRev, BudCom,
                      budrevcom, BudOldAdj)
SELECT     BUDID, Prid, CCID, BUDCBI, BUDCSI, BudCO, BUDName, BudValue, BUDLab, BudBurden, BUDMat, BUDSub, BUDVDID, Submittal, SubmitalTitle,
                      SubmitBy, SubmitCSI, ContractORpo, BudCoid, BudMatAdj, BudLabAdj, BudSubAdj, BudAdjId, BudCTD, BudCTC, AccountStatus, BudRev, BudCom,
                      budrevcom, BudOldAdj
FROM         dbo.tblBudget
0
Comment
Question by:aaronkempf
  • 5
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7112383
Where BUDCSI Like '%[0-9]%'

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7112386
Or
Where IsNumeric(cplname) = 1

However this assumes the whole string is numeric.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7112387
Typo.  I was testing with some local data, it should have been:
Where IsNumeric(BUDCSI) = 1

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7112393
Also, when you get a chance please maintain your open questions:
Home Network Date: 04/08/2002 12:02PM PST  
http://www.experts-exchange.com/jsp/qShow.jsp?ta=winnt&qid=20286168
Edit Link Bar Date: 04/10/2002 05:42PM PST
http://www.experts-exchange.com/jsp/qShow.jsp?ta=frontpage&qid=20287240

Thanks,
Anthony
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:aaronkempf
ID: 7112819
can i try this:

Where BUDCSI Like '%[0-9]%'

but instead do this:

Where BUDCSI NOT Like '%[a-z]%'
0
 
LVL 1

Author Comment

by:aaronkempf
ID: 7112822
sorry; i meant to say that this:
Where BUDCSI Like '%[0-9]%'

doesnt' suit my needs; because i have combined text & numeric there.

i need to look for the presence of text & or other characters; and i want to do it in a flexible, maintainable way.



0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 59 total points
ID: 7113818
Is this what you want:

Where BUDCSI Like '%[^0-9]%'

This will check for any characters NOT in the range 0-9 and is basically equivalent to:

Where IsNumeric(BUDCSI) = 0

Anthony
0
 
LVL 1

Author Comment

by:aaronkempf
ID: 7114329
ok lemme try that one--

also, i think that i am getting favorable results with IsNumeric-- ill have to look into it.. brb
0
 

Expert Comment

by:CleanupPing
ID: 9280225
aaronkempf:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now