IS NUMBER

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
LVL 1
aaronkempfAsked:
Who is Participating?
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
Where BUDCSI Like '%[0-9]%'

Anthony
0
 
Anthony PerkinsCommented:
Or
Where IsNumeric(cplname) = 1

However this assumes the whole string is numeric.

Anthony
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

Anthony
0
 
Anthony PerkinsCommented:
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
 
aaronkempfAuthor Commented:
can i try this:

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

but instead do this:

Where BUDCSI NOT Like '%[a-z]%'
0
 
aaronkempfAuthor Commented:
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
 
aaronkempfAuthor Commented:
ok lemme try that one--

also, i think that i am getting favorable results with IsNumeric-- ill have to look into it.. brb
0
 
CleanupPingCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.