Display odd non-Numeric numbers

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

General SQL Server Forums

New to SQL Server Programming

Display odd non-Numeric numbers

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-10-07 : 12:05:21

Hi,

I am trying to select only odd numbers from the sample list below. How would I do this? Please help...

5766 c
5867
4444 g
23423 f
23434 3
234234 p

thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:18:57

you've alpha data in column also?

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-10-07 : 12:24:55

Yes. I also have values such as

inhouse
abc
dbc
12214 c
12345 d
1232 f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:29:14

[code]SELECT column FROM Table
WHERE ISNUMERIC(column)= 1
AND column NOT LIKE '%d%'
AND column NOT LIKE '%e%'
AND column%2>0[/code]

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-10-07 : 12:39:27

How would I keep numbers with a dash?

for example:

30-345
3-4455

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:44:44

[code]SELECT column FROM Table
WHERE ISNUMERIC(REPLACE(column,'-',''))= 1
AND column NOT LIKE '%d%'
AND column NOT LIKE '%e%'
AND REPLACE(column,'-','')%2>0[/code]

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-10-07 : 12:45:33

Is it possible to trim the text out of values such as

c3434f
23423 c
cc23432

and add it to a temp table then select only odd numbers?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:54:33

quote:
Originally posted by nguyenl

Is it possible to trim the text out of values such as

c3434f
23423 c
cc23432

and add it to a temp table then select only odd numbers?

why do you keep on changing the requirements. first you told to get only odd numbers, then include ones with -. what's the exact output you're expecting.
Please give full details. what all types of data can come in your column and what all you need as output?

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-10-07 : 13:05:29

Sorry, the more I think about it the more combinations I see. I am trying to get a list of only odd non-numeric numbers from a list that contains:

c1234 c
c1235d
1234
23-234
2-345
5679 d

These are just sample data. When I use the Isnumeric() function I can isolate the Non-numerics out. But then I need a list of the odd non-numerics. For example:

2-345
c1235d
5679 d

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-07 : 13:25:40

Weird requirement.


SELECT *
FROM T
WHERE SUBSTRING(REVERSE(id), PATINDEX('%[0-9]%', REVERSE(id)), 1) LIKE '[13579]%'

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-10-07 : 14:26:10

Thanks. It seems to be working. Are you able to give me a little explanantion of what the function is doing. I would like to learn.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-07 : 16:30:50

It's testing if the last digit in the string is a 1, 3, 5, 7 or 9.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources