Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Brett Phipps (1/9/2013)

I've read the article and skimmed through all the comments looking if there was something that talked about how to actually do the cross apply as Jeff suggests, but I haven't seen an example of it.

I'm actually trying to implement this, but I'm just not getting it and was wondering if someone could help me out.

Here's the problem I'm trying to solve

I've got a list of product numbers that I need to validate are exclusively digits.

I created the function as specified ( renamed it to fn_isAllDigits to adhere to our naming standards and am trying to use it but I guess I just don't understand the how to properly format the query to use the cross apply.... Can someone help here's what I'm starting with:

Note: In this version of the query I'm trying to drill in and find all product numbers that are six digits long. The latest request was to include any rows that are longer than 6 digits but have leading zeroes that should also be included. eg. 00000001234546 should also be included) The simple solution seemed to be to just convert it to an int and then check the length as you see I attempted because the patindex check did not catch this: 0-3-101

SELECT DISTINCT PrimeProdNo
FROM ourDatabase.schema.tbl_product tp1
WHERE (6 = LEN(RTRIM(PrimeProdNo))
OR 6 = LEN(RTRIM(CAST(PrimeProdNo AS BIGINT)))) --I just added this to
AND PATINDEX('%[A-Z]%', PrimeProdNo) = 0 --this needs to be more than just a letter check
AND LEFT(PrimeProdNo, 1) > 0

Side Note 2. I got this code from someone else, but I was thinking about changing that from distinct to a group by on PrimeProdNo. Anyone have any comments on which is the better method to employ?

;with tbl_product(PrimeProdNo) as
(
select '383745' union all --valid
select '093847' union all --this is not length 6 so should not return
select '1234567' union all --this is length 7 so should not return
select 'asdf3e5' union all --non numerics
select '12(854' union all --non numerics
select '867596' union all --valid
select '0-3-101' --invalid
)
, tempResult as
(
select *
from tbl_product
cross apply dbo.IsAllDigits(PrimeProdNo)
)

select * from tempResult
where IsAllDigits = 1
and PrimeProdNo > 99999
and PrimeProdNo <= 999999

CREATE TABLE [ret].[tbl_product](
[LocationNo] [decimal](7, 0) NOT NULL,
[PrimeProdNo] [char](16) NOT NULL,
[ProdCategory] [char](3) NOT NULL,
[ProdCategoryHomeOffice] [char](3) NOT NULL,
[MFA] [varchar](5) NULL,
[ProdDesc] [char](30) NOT NULL,
[ProdDescHomeOffice] [char](30) NOT NULL,
[QuantityOnHand] [decimal](7, 0) NULL,
[ProdUomSale] [char](5) NOT NULL,
[ProdUomPurchase] [char](5) NOT NULL,
[ProdUomA] [char](5) NOT NULL,
[Density] [decimal](6, 2) NULL,
[RateA] [decimal](6, 2) NULL,
[LastSaleDate] [date] NULL,
[LastReceiptDate] [date] NULL,
[AddedDate] [date] NULL
) ON [PRIMARY]

GO

select *
from ret.tbl_product
cross apply Utilities.dbo.fn_isAllDigits(RTRIM(PrimeProdNo))
where IsAllDigits = 1
--and PrimeProdNo BETWEEN 100000 and 999999
AND (6 = LEN(RTRIM(PrimeProdNo))
--OR 6 = LEN(RTRIM(CAST(PrimeProdNo AS BIGINT)))
)

Msg 245, Level 16, State 1, Line 25
Conversion failed when converting the varchar value '3EHVHD ' to data type int.
Msg 248, Level 16, State 1, Line 25
The conversion of the varchar value '5510708105 ' overflowed an int column.

IF OBJECT_ID('tempdb..#tbl_product') IS NOT NULL
DROP TABLE #tbl_product

CREATE TABLE #tbl_product(
[PrimeProdNo] [char](16) NOT NULL

) ON [PRIMARY]

INSERT INTO #tbl_product
(PrimeProdNo)
select '383745' union all --valid
select '093847' union all --this is not length 6 so should not return
select '1234567' union all --this is length 7 so should not return
select 'asdf3e5' union all --non numerics
select '12(854' union all --non numerics
select '867596' union all --valid
select '0-3-101' UNION ALL --invalid
SELECT '0-3-101 ' UNION ALL
SELECT '00161B '

SELECT * FROM #tbl_product AS tp

select *
from #tbl_product
cross apply Utilities.dbo.fn_isAllDigits(RTRIM(PrimeProdNo))
where IsAllDigits = 1
and PrimeProdNo BETWEEN 100000 and 999999
AND (6 = LEN(RTRIM(PrimeProdNo))
--OR 6 = LEN(RTRIM(CAST(PrimeProdNo AS BIGINT)))
)

select *
from ret.tbl_product
CROSS APPLY Utilities.dbo.fn_isAllDigits(RTRIM(PrimeProdNo))
where IsAllDigits = 1
--and PrimeProdNo BETWEEN 100000 and 999999
AND (6 = LEN(RTRIM(PrimeProdNo))
OR 6 = CASE WHEN IsAllDigits = 1 THEN LEN(RTRIM(CAST(PrimeProdNo AS BIGINT))) ELSE 0 END
)