Excluding FileNbr with an Alpha character

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Excluding FileNbr with an Alpha character

Author

Topic

bricktop
Starting Member

2 Posts

Posted - 2005-05-25 : 15:08:35

I need to exclude all of my FileNbr WHERE the right most character is an alpha character and can vary.Example:
FileNbr
1234
1235
123A
123S
Results expected
1234
1235
This is what I have so far:
SELECT * FROM TEMP
WHERE (RIGHT(FileNbr,1)<> ??

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2005-05-25 : 15:18:41

Use the ISNUMERIC function to determine if the last character is not numeric.

Ex:
SELECT * FROM TEMP WHERE ISNUMERIC(RIGHT(FileNbr,1)) = 1

bricktop
Starting Member

2 Posts

Posted - 2005-05-25 : 16:33:26

Thanks it works great. so if I wanted to keep only the records that had an alpha I would change the =1 to ?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-25 : 16:41:45

0

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-26 : 09:32:03

bear in mind that isnumeric also returns true for this:
1234e5

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-26 : 10:02:20

if you have data as spirit1 specified, then you need to use this to exclude that data

SELECT * FROM YOURTABLE WHERE ISNUMERIC(FileNbr) = 1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources