A Little About Why ISNUMERIC Sucks In SQL Server
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Related Posts
- Why Read Committed Queries Can Still Return Bad Results In SQL Server
- Join Me And Kendra Little At PASS For Two Days Of SQL Server Performance Tuning Precons
- Updates To sp_QuickieStore, sp_HumanEventsBlockViewer, and sp_PressureDetector
- Join me In Boston May 10 For A Full Day Of SQL Server Performance Tuning Training
I think your regex based solution fails on simple cases. For example, you’re trying to see if the test value is a bigint. -1 is a valid bigint but your code flags it as not valid. Would `try_cast(value as DT) is not null` suffice to get around “maybe it’s null, maybe it’s 0”?
It’s easy enough to accommodate negative numbers. I just don’t run into needing to test for those very often.
Im a little dim witted myself but I use this (below). Sadly i deal with some older systems that insist on leading zeros in numbers as strings in tables, so the regex alone wont do the trick.
DROP TABLE #numerictest
;
CREATE TABLE #NumericTest (id int IDENTITY(1,1) not null, string varchar(25))
;
INSERT INTO #NumericTest (string) VALUES
(‘0123’),
(‘3245’),
(‘thiswillfail’),
(‘justlikeYOU23’),
(‘34567’+CHAR(13))
;
SELECT
nt.id
, nt.string
, ‘IsNumeric’ =
CASE
WHEN SUM
(
CASE
WHEN z.value NOT LIKE ‘%[^0-9]%’ THEN 0
ELSE 1
END
) > 0
OR
LEFT(string,1)=’0′ THEN 0
ELSE 1
END
FROM
#NumericTest nt
OUTER APPLY STRING_SPLIT(CAST(CAST(CAST
(nt.string AS NVARCHAR) AS VARBINARY) AS VARCHAR), CHAR(0)) z
WHERE
z.value ”
GROUP BY
nt.id
, nt.string
🥴🥴🥴