December 10, 2014 at 10:00 am
Hello,
I am trying to use the function DigitsOnly by Jeff Moden. See: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360 but I am getting an error. I assume I did something wrong. 😀
My data and execute code:
CREATE TABLE MyPhone (RefNum VARCHAR(10), Phone VARCHAR(20));
INSERT INTO MyPhone (RefNum, Phone) VALUES
('1135536','5555112733'),
('1135536',NULL),
('1135536','5552347604'),
('1135537','55542X3255'),
('1135537','55542X5625'),
('1135537','55547X8187'),
('1135537',NULL);
SELECT MP.RefNum, MP.Phone, DO.DigitsOnly
FROM MyPhone MP
CROSS APPLY DigitsOnly(Phone) DO;
This gives the error:
Msg 1014, Level 15, State 1, Line 11
TOP clause contains an invalid value.
Here is the function that I am using:
CREATE FUNCTION dbo.DigitsOnly
/******************************************************************************************************************************
Purpose:
Given a VARCHAR(8000) or less string, return only the numeric digits from the string.
Programmer's Notes:
1. This is an iTVF (Inline Table Valued Function) that will be used as an iSF (Inline Scalar Function) in that it returns a
single value in the returned table and should normally be used in the FROM clause as with any other iTVF.
2. CHECKSUM returns an INT and will return the exact number given if given an INT to begin with. It's also faster than a CAST
or CONVERT and is used as a performance enhancer by changing the BIGINT of ROW_NUMBER() to a more appropriately sized INT.
3. Another performance enhancement is using a WHERE clause calculation to prevent the relatively expensive XML PATH
concatentation of empty strings normally determined by a CASE statement in the XML "loop".
4. Another performance enhancement is not making this function a generic function that could handle a pattern. That allows
us to use all integer math to do the comparison using the high speed ASCII function convert characters to their numeric
equivalent. ASCII characters 48 through 57 are the digit characters of 0 through 9.
Kudos:
1. Hats off to Eirikur Eiriksson for the ASCII conversion idea and for the reminders that dedicated functions will always
be faster than generic functions and that integer math beats the tar out of character comparisons that use LIKE or
PATINDEX.
2. Hats off to all of the good people that submitted and tested their code on the following thread. It's this type of
participation and interest that makes code better. You've just gotta love this commmunity.
http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360
Usage:
--===== CROSS APPLY example
SELECT ca.DigitsOnly
FROM dbo.SomeTable
CROSS APPLY dbo.DigitsOnly(SomeVarcharCol) ca
;
Revision History:
Rev 00 - 29 Oct 2014 - Jeff Moden - Initial Creation
-
******************************************************************************************************************************/
--===== Declare the I/O for this function
(@pString VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,Tally(N) AS (SELECT TOP (LEN(@pString)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 a,E1 b,E1 c,E1 d)
SELECT DigitsOnly =
(
SELECT SUBSTRING(@pString,N,1)
FROM Tally
WHERE ASCII(SUBSTRING(@pString,N,1)) BETWEEN 48 AND 57
FOR XML PATH('')
)
;
December 10, 2014 at 10:17 am
Thanks!
I am looking to replace our current phone scrub with something a bit faster so I will make sure no NULLS.
December 10, 2014 at 12:33 pm
Good catch, never tested the functions for a null value:pinch:, here is a revised version
😎
/* Eirikur's modification of Jeff's Modification of Erikur's Function */
GO
IF OBJECT_ID('dbo.DigitsOnlyEE') IS NOT NULL DROP FUNCTION dbo.DigitsOnlyEE;
GO
CREATE FUNCTION dbo.DigitsOnlyEE
(@pString VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,Tally(N) AS (SELECT TOP (LEN(ISNULL(@pString,CHAR(32)))) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 a,E1 b,E1 c,E1 d)
SELECT DigitsOnly =
(
SELECT SUBSTRING(@pString,N,1)
FROM Tally
WHERE ((ASCII(SUBSTRING(@pString,N,1)) - 48) & 0x7FFF) < 10
FOR XML PATH('')
)
;
GO
and your test case
CREATE TABLE MyPhone (RefNum VARCHAR(10), Phone VARCHAR(20));
INSERT INTO MyPhone (RefNum, Phone) VALUES
('1135536','5555112733'),
('1135536',NULL),
('1135536','5552347604'),
('1135537','55542X3255'),
('1135537','55542X5625'),
('1135537','55547X8187'),
('1135537',NULL);
SELECT MP.RefNum, MP.Phone, DO.DigitsOnly
FROM MyPhone MP
CROSS APPLY DigitsOnlyEE(Phone) DO;
Results
RefNum Phone DigitsOnly
---------- -------------------- ------------
1135536 5555112733 5555112733
1135536 NULL NULL
1135536 5552347604 5552347604
1135537 55542X3255 555423255
1135537 55542X5625 555425625
1135537 55547X8187 555478187
1135537 NULL NULL
December 10, 2014 at 12:51 pm
Thanks Eirikur. just one question, what does "& 0x7FFF" do? I realize that the & is for "bitwise and", however I am not very good at that.
December 10, 2014 at 1:17 pm
I'm curious (general question to everyone)...
Wouldn't this be faster?
-- the function
CREATE FUNCTION dbo.DIGITSONLYAB(@pstring varchar(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT ISALLDIGITS = CASE PATINDEX('%[^0-9]%',@pstring) WHEN 0 THEN 1 ELSE 0 END;
GO
-- use:
WITH x AS
(
SELECT val = v
FROM (VALUES ('11.0'),('123r'),('123'),('0123'),(NULL)) t(v)
)
SELECT *
FROM x
CROSS APPLY dbo.DIGITSONLYAB(val);
...handles NULLs too :hehe:
Edit: screwed up my example code; added note about NULLs
:blush:
-- Itzik Ben-Gan 2001
December 10, 2014 at 1:37 pm
djj (12/10/2014)
Thanks Eirikur. just one question, what does "& 0x7FFF" do? I realize that the & is for "bitwise and", however I am not very good at that.
Its a simple trick to shift all the negative values to the top of the range so a single operator can be applied, a lot less expensive than using between.
😎
December 10, 2014 at 1:39 pm
Alan.B (12/10/2014)
I'm curious (general question to everyone)...Wouldn't this be faster?
-- the function
CREATE FUNCTION dbo.DIGITSONLYAB(@pstring varchar(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT ISALLDIGITS = CASE PATINDEX('%[^0-9]%',@pstring) WHEN 0 THEN 1 ELSE 0 END;
GO
-- use:
WITH x AS
(
SELECT val = v
FROM (VALUES ('11.0'),('123r'),('123'),('0123'),(NULL)) t(v)
)
SELECT *
FROM x
CROSS APPLY dbo.DIGITSONLYAB(val);
...handles NULLs too :hehe:
Edit: screwed up my example code; added note about NULLs
Cannot tell if it is faster without testing but certainly looks cleaner;-)
😎
December 11, 2014 at 9:07 am
Alan.B (12/10/2014)
I'm curious (general question to everyone)...Wouldn't this be faster?
-- the function
CREATE FUNCTION dbo.DIGITSONLYAB(@pstring varchar(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT ISALLDIGITS = CASE PATINDEX('%[^0-9]%',@pstring) WHEN 0 THEN 1 ELSE 0 END;
GO
-- use:
WITH x AS
(
SELECT val = v
FROM (VALUES ('11.0'),('123r'),('123'),('0123'),(NULL)) t(v)
)
SELECT *
FROM x
CROSS APPLY dbo.DIGITSONLYAB(val);
...handles NULLs too :hehe:
Edit: screwed up my example code; added note about NULLs
It is irrelevant id it is faster or not, as it does not implement the same functionality.
It just returns True/False, not a "cleared" digit-only string.
You don't really need to wrap it to UDF at all, just do:
SELECT *
FROM (VALUES ('11.0'),('123r'),('123'),('0123'),(NULL)) t(val)
CROSS APPLY (select CASE PATINDEX('%[^0-9]%',val) WHEN 0 THEN 1 ELSE 0 END) Q(IsAllDigits)
BTW. I think there is only one way to win over DigitsOnlyEE iTVF in perfotmance (especaially for large strings), it would be good-written CLR...
December 13, 2014 at 1:35 pm
Eugene Elutin (12/11/2014)
Alan.B (12/10/2014)
I'm curious (general question to everyone)...Wouldn't this be faster?
-- the function
CREATE FUNCTION dbo.DIGITSONLYAB(@pstring varchar(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT ISALLDIGITS = CASE PATINDEX('%[^0-9]%',@pstring) WHEN 0 THEN 1 ELSE 0 END;
GO
-- use:
WITH x AS
(
SELECT val = v
FROM (VALUES ('11.0'),('123r'),('123'),('0123'),(NULL)) t(v)
)
SELECT *
FROM x
CROSS APPLY dbo.DIGITSONLYAB(val);
...handles NULLs too :hehe:
Edit: screwed up my example code; added note about NULLs
It is irrelevant id it is faster or not, as it does not implement the same functionality.
It just returns True/False, not a "cleared" digit-only string.
You don't really need to wrap it to UDF at all, just do:
SELECT *
FROM (VALUES ('11.0'),('123r'),('123'),('0123'),(NULL)) t(val)
CROSS APPLY (select CASE PATINDEX('%[^0-9]%',val) WHEN 0 THEN 1 ELSE 0 END) Q(IsAllDigits)
I feel stupid, stupid this morning (smacking forehead). For some reason I thought I was looking an ISALLDIGITS function. That said, I use Eirikur's DigitsOnlyEE and it is excellent.
-- Itzik Ben-Gan 2001
December 16, 2014 at 3:52 pm
It's only 20 chars, I'd do it as simply as possible rather than over-complicate it:
SELECT MP.RefNum, MP.Phone,
CASE WHEN SUBSTRING(MP.Phone, 01, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 01, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 02, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 02, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 03, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 03, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 04, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 04, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 05, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 05, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 06, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 06, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 07, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 07, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 08, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 08, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 09, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 09, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 10, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 10, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 11, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 11, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 12, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 12, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 13, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 13, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 14, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 14, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 15, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 15, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 16, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 16, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 17, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 17, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 18, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 18, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 19, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 19, 1) ELSE '' END +
CASE WHEN SUBSTRING(MP.Phone, 20, 1) LIKE '[0-9]' THEN SUBSTRING(MP.Phone, 20, 1) ELSE '' END AS DigitsOnly
FROM MyPhone MP
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply