I want to display only my numeric values in SQL

I have a query that it has a lot of filters to avoid letters or special characters, I was wondering If there is a way to filter the column with one statement to displays only the numbers

here is the sample code

select /*top 200*/

v.arpnumber, o.last, o.First, v.coOwnerFn, v.coOwnerLn, o.address1, o.address2, o.city, o.zip, o.state,o.province,o.country, o.datecreated, o.login, o.pwd

from ownersdb.dbo.AffinityRewardsMembers o ,dbo.contacts v

where system = 'lead'

and o.armembernum = v.arpnumber

and v.contacttype = 'owner'

and v.arpnumber is not null

and v.arpnumber <> ''

and v.arpnumber not between 'a%' and 'z%'

and v.arpnumber not between '%a%' and '%z%'

and v.arpnumber not like '% %'

and v.arpnumber not like '%pk%'

and v.arpnumber not like '%pb%'

and v.arpnumber not like '%z%'

and v.arpnumber not like '%w%'

and o.ownerid is not null

and v.officeid in ('37','32','30','31','20','22','23')

and o.ReportPrinted = 0

and o.login is not null

and o.pwd is not null

/*and city is not null

and city <> ''*/

order by v.ln

the column I'm trying is arpNumber

instead of using all these filter I just want to put

i.e

v.arpnumber is numeric

Any suggestions ?

Thanks in advance

One way you can do that is by writing a user defined function called isNumeric using .net CLR with regular expressions.

Then you can check the arpNumber if it is numeric or not in one word.

|||

"Isnumeric" also has issues. Give a look here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Despite this, a quick and dirty work-around that I sometimes use (that is NOT foolproof!) is to check

isNumeric (@.aStringToBeTested + 'D2') = 1

This eliminates some of the problems -- it eliminates the money "marker" and it eliminates many "float" datatype problems. However, please give the writeup a look. (and like Dale says, give the IsReallyNumeric function a look.)

|||

Try using the isReallyNumeric function found here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

(Thanks Arnie)

Add the function to your database then

replace your pattern tests with

Code Snippet

and dbo.isReallyNumeric(v.arpnumber)=1

|||

I guess I am a bit confused.

Do you want ot ONLY RETRIEVE rows where [v.arpnumber] contains only numbers,
OR,

Do you want to DISPLAY the [v.arpnumber] field, stripping out any non-numeric characters?

If it the first, then DaleJ's suggestion is good and should work for you, otherwise you will need a function similar to the following:

Code Snippet

CREATE FUNCTION dbo.fnNumbersOnly
( @.InParam varchar(500) )
RETURNS varchar(500)
AS
BEGIN
IF patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
WHILE patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
SET @.InParam = Stuff( @.InParam, patindex( '%[^0-9]%', @.InParam), 1, '' )
END
END
RETURN @.InParam
END
GO

-- Test
DECLARE @.TestParam varchar(50)
SET @.TestParam = 'ab3452SDF094c&h*¤,1r340.95849%eew#y/'
SELECT dbo.fnNumbersOnly( @.TestParam )

--
3452094134095849

Usage:

SELECT TOP 200
arpnumber = dbo.fnNumbersOnly( v.arpnumber ),
o.last,
etc.,

|||

as a matter of fact i'm trying to retrieve data that will update another store and these values most be numeric, I'll check that func that I read that seems to be a good idea.

sql