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