Column
fc-5484
grt-98568
htyyy-45813
tpl-986999
I need Number Only
Column
5484
98568
45813
986999
Before we get started, there are two performance tips that you might want to adopt.
The first is to NOT use SCALAR User Defined Functions. They're 7 times slower than using straight code and 7 times slower than using an "iSF" (Inline Scalar Function), which is really an "iTVF" (Inline Table Valued Function) that only returns a single element. There's also thing known as an "mTVF", which is even worse than a SCALAR function. To sum things up, if you have to use the word BEGIN in your function, you've NOT written a high performance iTVF.
Please see the following article for an explanation of how to make an "iSF".
How to Make Scalar UDFs Run Faster (SQL Spackle)
The second thing to understand is using a WHILE loop to count anything, characters, rows, etc, is normally a REALLY bad idea (and Recursive CTE's can be worse). As you can well imagine, nested WHILE loops are much worse. WHILE loops and many Recursive CTEs (rCTE) fall into a category of code called "RBAR", which is a term I made up many years ago for "Row By Agonizing Row"" and is pronounced "ReBar", like the iron stuck in cement forever.
You still need something like a WHILE loop to solve this problem in T-SQL, but we know we shouldn't use either a WHILE loop or an rCTE to solve the problem. What can we use instead? A "Pseudo Cursor", which is really the high speed looping that occurs behind the SELECT. Please see the following article for more on that subject.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Using the CROSS JOIN version of a Tally Table (thank you Itizik Ben-Gan), an iTVF disguised as an iSF, and some basic T-SQL functionality, we end up with a function that looks like this. To use it, see the "USAGE" example in the comments in the header.
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 (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,Tally(N) AS (SELECT TOP (LEN(@pString)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT 1))) 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('')
)
;select case charindex('-',prname,1)
when 0 then ' '
else
substring(prname,charindex('-',prname,1)+1,len(prname))
end
from prodcuts