do you have a StripNonNumeric ITVF function?

  • Little clean up in the logic, now it looks pretty good

    😎

    /********************************************************************

    -- Stripping out any non-numerical characters

    -- EE 2014-10-28 Inital coding

    -- EE Cleaned up comparison logic

    ********************************************************************/

    ALTER FUNCTION dbo.STRIP_NUM_EE

    (

    @INSTR VARCHAR(8000)

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    ,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

    SELECT

    (

    SELECT

    CASE WHEN (ASCII(SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) - 48) BETWEEN 0 AND 9 THEN SUBSTRING(@INSTR,NM.N,1) END

    FROM NUMS NM

    FOR XML PATH('')

    ) AS OUT_STR

    ;

    Test results

    Beginning execution loop

    ========== Using nGrams ==========

    1666

    ========== Using nGrams ==========

    1680

    ========== Using nGrams ==========

    1706

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Using PatExclude8K ==========

    2830

    ========== Using PatExclude8K ==========

    2756

    ========== Using PatExclude8K ==========

    2750

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Jeff's Old Scalar Loop Function ==========

    1596

    ========== Jeff's Old Scalar Loop Function ==========

    1556

    ========== Jeff's Old Scalar Loop Function ==========

    1556

    Batch execution completed 3 times.

    Beginning execution loop

    ========== dbo.STRIP_NUM_EE Function ==========

    1393

    ========== dbo.STRIP_NUM_EE Function ==========

    1343

    ========== dbo.STRIP_NUM_EE Function ==========

    1343

    Batch execution completed 3 times.

  • Eirikur Eiriksson (10/28/2014)


    Little clean up in the logic, now it looks pretty good

    😎

    /********************************************************************

    -- Stripping out any non-numerical characters

    -- EE 2014-10-28 Inital coding

    -- EE Cleaned up comparison logic

    ********************************************************************/

    ALTER FUNCTION dbo.STRIP_NUM_EE

    (

    @INSTR VARCHAR(8000)

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    ,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

    SELECT

    (

    SELECT

    CASE WHEN (ASCII(SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) - 48) BETWEEN 0 AND 9 THEN SUBSTRING(@INSTR,NM.N,1) END

    FROM NUMS NM

    FOR XML PATH('')

    ) AS OUT_STR

    ;

    Test results

    Beginning execution loop

    ========== Using nGrams ==========

    1666

    ========== Using nGrams ==========

    1680

    ========== Using nGrams ==========

    1706

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Using PatExclude8K ==========

    2830

    ========== Using PatExclude8K ==========

    2756

    ========== Using PatExclude8K ==========

    2750

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Jeff's Old Scalar Loop Function ==========

    1596

    ========== Jeff's Old Scalar Loop Function ==========

    1556

    ========== Jeff's Old Scalar Loop Function ==========

    1556

    Batch execution completed 3 times.

    Beginning execution loop

    ========== dbo.STRIP_NUM_EE Function ==========

    1393

    ========== dbo.STRIP_NUM_EE Function ==========

    1343

    ========== dbo.STRIP_NUM_EE Function ==========

    1343

    Batch execution completed 3 times.

    Brilliant work sir - very well done!

    What is a little weird is how PatExclude8K is performing so bad on your system. It is doing much better than the nGrams solution on my system. Other than that I get similar results.

    Beginning execution loop

    ========== Using nGrams ==========

    2416

    ========== Using nGrams ==========

    2420

    ========== Using nGrams ==========

    2390

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Using PatExclude8K ==========

    2053

    ========== Using PatExclude8K ==========

    2013

    ========== Using PatExclude8K ==========

    2043

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Jeff's Old Scalar Loop Function ==========

    1840

    ========== Jeff's Old Scalar Loop Function ==========

    1860

    ========== Jeff's Old Scalar Loop Function ==========

    1840

    Batch execution completed 3 times.

    Beginning execution loop

    ========== dbo.STRIP_NUM_EE Function ==========

    1690

    ========== dbo.STRIP_NUM_EE Function ==========

    1690

    ========== dbo.STRIP_NUM_EE Function ==========

    1673

    Batch execution completed 3 times.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Eirikur Eiriksson (10/28/2014)


    Little clean up in the logic, now it looks pretty good

    😎

    /********************************************************************

    -- Stripping out any non-numerical characters

    -- EE 2014-10-28 Inital coding

    -- EE Cleaned up comparison logic

    ********************************************************************/

    ALTER FUNCTION dbo.STRIP_NUM_EE

    (

    @INSTR VARCHAR(8000)

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    ,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

    SELECT

    (

    SELECT

    CASE WHEN (ASCII(SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) - 48) BETWEEN 0 AND 9 THEN SUBSTRING(@INSTR,NM.N,1) END

    FROM NUMS NM

    FOR XML PATH('')

    ) AS OUT_STR

    ;

    Test results

    Beginning execution loop

    ========== Using nGrams ==========

    1666

    ========== Using nGrams ==========

    1680

    ========== Using nGrams ==========

    1706

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Using PatExclude8K ==========

    2830

    ========== Using PatExclude8K ==========

    2756

    ========== Using PatExclude8K ==========

    2750

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Jeff's Old Scalar Loop Function ==========

    1596

    ========== Jeff's Old Scalar Loop Function ==========

    1556

    ========== Jeff's Old Scalar Loop Function ==========

    1556

    Batch execution completed 3 times.

    Beginning execution loop

    ========== dbo.STRIP_NUM_EE Function ==========

    1393

    ========== dbo.STRIP_NUM_EE Function ==========

    1343

    ========== dbo.STRIP_NUM_EE Function ==========

    1343

    Batch execution completed 3 times.

    You've just gotta love pure math instead of character based calculations. Thanks and well done, Eirikur. Now I have to test similar logic in a While Loop and see if that's any faster.

    It also shows that general purpose functions have general purpose performance. Functions with a specific purpose are usually faster. It's a lesson that I learned a long time ago and have apparently lost my mind because I sure didn't apply it here. So, thinks for the well deserved kick in the head to shake me out of it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (10/28/2014)


    I took the two contenders from your good post, Alan, and added on of my own. Here's the code for the function I use for such things. Yeah... you'll be shocked. It's not only a scalar function but it also has a WHILE loop in it.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[CleanString]

    /*******************************************************************************

    Purpose:

    Given a string and a pattern of characters to remove, remove the patterned

    characters from the string.

    Usage:

    --===== Basic Syntax Example

    SELECT CleanedString = dbo.CleanString(@pSomeString,@pPattern)

    ;

    --===== Remove all but Alpha characters

    SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^A-Za-z]%');

    FROM dbo.SomeTable st

    ;

    --===== Remove all but Numeric digits

    SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^0-9]%');

    FROM dbo.SomeTable st

    ;

    Programmer Notes:

    1. @pPattern is case sensitive.

    2. The pattern set of characters must be for just one character.

    Revision History:

    Rev 00 - Circa 2007 - George Mastros?

    - Initial find on the web

    Rev 01 - 29 Mar 2007 - Jeff Moden

    - Optimize to remove one instance of PATINDEX from the loop.

    - Add code to use the pattern as a parameter.

    Rev 02 - 26 May 2013 - Jeff Moden

    - Add case sensitivity

    *******************************************************************************/

    (@pString VARCHAR(8000),@pPattern VARCHAR(100))

    RETURNS VARCHAR(8000) AS

    BEGIN

    DECLARE @Pos SMALLINT;

    SELECT @Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);

    WHILE @Pos > 0

    SELECT @pString = STUFF(@pString,@Pos,1,''),

    @Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);

    RETURN @pString;

    END

    ;

    Here's the test harness that I used. It runs each function through the 100K row table 3 times.

    --===== Create the 100K row test table

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

    ;

    SELECT TOP 100000

    txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')

    INTO #val

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    ALTER TABLE #Val

    ADD PRIMARY KEY CLUSTERED (txt)

    ;

    --===== Do the tests. Had to use duration because one

    -- of the tests is on the new scalar function and

    -- SET STATISTICS doesn't report on those correctly.

    GO

    PRINT '========== Using nGrams ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @string = CleanedText

    FROM #val

    CROSS APPLY dbo.StripNonNumeric_itvf_ajb(txt)

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    PRINT '========== Using PatExclude8K ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @string = newstring

    FROM #val

    CROSS APPLY dbo.PatExclude8K(txt, '[^0-9]')

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    PRINT '========== Jeff''s Old Scalar Loop Function ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @String = dbo.CleanString(txt,'%[^0-9]%')

    FROM #val

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    Here are the run results. Again, you'll be shocked. This is the one place where I've not been able to make a Tally Table solution able to beat it. Lord knows I and other good folks have tried.

    (100000 row(s) affected)

    Beginning execution loop

    ========== Using nGrams ==========

    2916

    ========== Using nGrams ==========

    2893

    ========== Using nGrams ==========

    2890

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Using PatExclude8K ==========

    2703

    ========== Using PatExclude8K ==========

    2640

    ========== Using PatExclude8K ==========

    2653

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Jeff's Old Scalar Loop Function ==========

    2413

    ========== Jeff's Old Scalar Loop Function ==========

    2500

    ========== Jeff's Old Scalar Loop Function ==========

    2466

    Batch execution completed 3 times.

    That is an excellent function Jeff, thanks for sharing that. It took a a few reads to understand how it works. Interesting to see a scalar function with a loop perform so well. I've tested Eirikur's solution on and it is getting the best performance. I started to make a version of Eirikur's that takes a pattern but I'm out of gas for the day. I am going to re-factor his code in the morning to take a pattern as a parameter and re-test the performance.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • So I made the following change to Eirikur's function to so that it takes a pattern as a parameter:

    -- refactoring...

    CREATE FUNCTION dbo.STRIP_NUM_EE_PAT

    (

    @INSTR VARCHAR(8000),

    @PATTERN VARCHAR(50)

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    ,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

    SELECT

    (

    SELECT

    CASE WHEN PATINDEX(@PATTERN,SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) = 0 THEN SUBSTRING(@INSTR,NM.N,1) END

    FROM NUMS NM

    FOR XML PATH('')

    ) AS OUT_STR

    ;

    GO

    and ran the following test a few times:

    --===== Create the 100K row test table

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

    ;

    SELECT TOP 100000

    txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')

    INTO #val

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    ALTER TABLE #Val

    ADD PRIMARY KEY CLUSTERED (txt)

    ;

    --===== Do the tests. Had to use duration because one

    -- of the tests is on the new scalar function and

    -- SET STATISTICS doesn't report on those correctly.

    GO

    PRINT '========== Using PatExclude8K ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @string = newstring

    FROM #val

    CROSS APPLY dbo.PatExclude8K(txt, '[^0-9]')

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    PRINT '========== Jeff''s Old Scalar Loop Function ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @String = dbo.CleanString(txt,'%[^0-9]%')

    FROM #val

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    PRINT '========== EE ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @string = OUT_STR

    FROM #val

    CROSS APPLY dbo.STRIP_NUM_EE(txt);

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE());

    GO 3

    PRINT '========== EE_PAT ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @string = OUT_STR

    FROM #val

    CROSS APPLY dbo.STRIP_NUM_EE_PAT(txt,'[^0-9]');

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE());

    GO 3

    Though the new version appears to produce the exact some query plan it the version that uses PATINDEX seems to perform a little faster....

    Beginning execution loop

    ========== Using PatExclude8K ==========

    1993

    ========== Using PatExclude8K ==========

    2073

    ========== Using PatExclude8K ==========

    1993

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Jeff's Old Scalar Loop Function ==========

    1803

    ========== Jeff's Old Scalar Loop Function ==========

    1840

    ========== Jeff's Old Scalar Loop Function ==========

    1810

    Batch execution completed 3 times.

    Beginning execution loop

    ========== EE ==========

    1766

    ========== EE ==========

    1716

    ========== EE ==========

    1703

    Batch execution completed 3 times.

    Beginning execution loop

    ========== EE_PAT ==========

    1626

    ========== EE_PAT ==========

    1576

    ========== EE_PAT ==========

    1563

    Batch execution completed 3 times.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • First, hats off to Eirikur Eiriksson for the reminders about the performance of dedicated rather than general purpose functions and the speed associated with integer comparisons rather than character-based comparisons. With that, I made a couple of tweaks to Eirikur's good code for another bit of performance. Here's the function I came up with as a result. As usual, details are in the code. If someone needs this for 2005, just change the VALUES function to 10 individual SELECT NULL UNION ALL statements (the last one not having UNION ALL) and it'll work in 2005.

    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('')

    )

    ;

    Here's the test-harness code that I used for the performance tests. It uses the same table we've been using.

    PRINT '========== Erikur''s Function ==========';

    GO

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @String = OUT_STR

    FROM #val

    CROSS APPLY dbo.STRIP_NUM_EE(txt)

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    PRINT '========== Jeff''s Modification of Erikur''s Function ==========';

    GO

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @String = ca.DigitsOnly

    FROM #val

    CROSS APPLY dbo.DigitsOnly(txt) ca

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    Here are the run results from the same test harness that we've been using...

    ========== Erikur's Function ==========

    Beginning execution loop

    2626

    2650

    2563

    Batch execution completed 3 times.

    ========== Jeff's Modification of Erikur's Function ==========

    Beginning execution loop

    2083

    2073

    2133

    Batch execution completed 3 times.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Alan.B (10/29/2014)


    So I made the following change to Eirikur's function to so that it takes a pattern as a parameter:

    Interesting... (our posts crossed paths). I'll take a look at that tonight, Alan. Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks Alan and Jeff, very good job indeed!

    This is really the SSC community at it's best and although I haven't had any time to look properly into this, Alan and Jeff have picked it up and pushed it further, Chapeau!

    Looking at Jeff's brilliant improvements (nice trick with the checksum!), I can see an opportunity for further improvement, mainly the logical operator BETWEEN. It has roughly 1/4 of the total cost. I replaced it with a single comparison operator using a little bit-bashing and got the filter cost down to about 1/5.

    😎

    CREATE FUNCTION dbo.DigitsOnlyEE

    /* Jeff's comment from his function goes here */

    (@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)) - 48) & 0x7FFF) < 10

    FOR XML PATH('')

    )

    ;

    GO

    Test result using Jeff's test harness

    Beginning execution loop

    ========== dbo.DigitsOnly Function ==========

    1306

    ========== dbo.DigitsOnly Function ==========

    1286

    ========== dbo.DigitsOnly Function ==========

    1310

    Batch execution completed 3 times.

    Beginning execution loop

    ========== dbo.DigitsOnlyEE Function ==========

    1113

    ========== dbo.DigitsOnlyEE Function ==========

    1123

    ========== dbo.DigitsOnlyEE Function ==========

    1153

    Batch execution completed 3 times.

    Edit: missed the second byte 0x7FFF.

  • Tweaking the code a little bit more shaves off approximately 17 percent, here is an all in one code

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    --===== Create the 100K row test table

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

    ;

    SELECT TOP 1000000

    txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')

    INTO #val

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    ALTER TABLE #Val

    ADD PRIMARY KEY CLUSTERED (txt)

    ;

    GO

    /* Jeff's Modification of Erikur's Function */

    IF OBJECT_ID('dbo.DigitsOnly') IS NOT NULL DROP FUNCTION dbo.DigitsOnly;

    GO

    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('')

    )

    ;

    /* 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(@pString)) (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

    PRINT '========== Jeff''s Modification of Erikur''s Function ==========';

    GO

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @String = ca.DigitsOnly

    FROM #val

    CROSS APPLY dbo.DigitsOnly(txt) ca

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    PRINT '========== Eirikur''s modification of Jeff''s Modification of Erikur''s Function ==========';

    GO

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME

    ;

    SELECT @StartTime = GETDATE()

    ;

    SELECT @String = ca.DigitsOnly

    FROM #val

    CROSS APPLY dbo.DigitsOnlyEE(txt) ca

    ;

    PRINT DATEDIFF(ms,@StartTime,GETDATE())

    ;

    GO 3

    Results

    ========== Jeff's Modification of Erikur's Function ==========

    Beginning execution loop

    11613

    11630

    11763

    Batch execution completed 3 times.

    ========== Eirikur's modification of Jeff's Modification of Erikur's Function ==========

    Beginning execution loop

    9700

    9666

    9663

    Batch execution completed 3 times.

  • Now we're cooking with gas! Well done! I had eye surgery today (lens replacement) and can't see so well yet and can't spend much time in front of the computer but I'll run some additional tests over the weekend using variable length data and see what happens.

    We also need to try the same thing on Alan's good pattern matching code.

    As Eirikur suggests, this is an example of the SQL Server Community (same initials as SQL Server Central :-)) at it's best. Keep 'em coming folks!

    BTW, that's one heck of a nice machine you have there, Eirikur!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Adding some gas supplies for the stove, first there are five methods for replacing logical operators or comparison operator combination equivalent thereof with a single comparison operator.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @START_NUM INT = 48; -- ASCII Code for 0 (zero)

    DECLARE @SAMPLE_SIZE INT = 256; -- Number of ASCII Characters, counting from 0 to 255

    ;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 (@SAMPLE_SIZE) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) -1 FROM E1 a,E1 b,E1 c,E1 d)

    SELECT

    T.N AS CH_No

    ,CHAR(T.N ) AS TChar

    /* Less than 10 */

    ,((T.N ) - @START_NUM) & 0x7FFF AS LT_10

    /* Equal to 0 */

    ,(((T.N ) - @START_NUM) & 0x7FFF) / 10 AS EQ_0

    /* Equal to 9 */

    ,ABS(((T.N ) - 48)) + ABS((T.N) - 57) AS EQ_9

    /* Equal to 1 */

    ,((T.N ) - 38) / 10 AS EQ_1

    /* Equal to 5 */

    ,(T.N + 2) / 10 AS EQ_5

    FROM Tally T;

    Results

    CH_No TChar LT_10 EQ_0 EQ_9 EQ_1 EQ_5

    -------------------- ----- -------------------- -------------------- -------------------- -------------------- --------------------

    0 32720 3272 105 -3 0

    1 32721 3272 103 -3 0

    2 32722 3272 101 -3 0

    3 32723 3272 99 -3 0

    4 32724 3272 97 -3 0

    5 32725 3272 95 -3 0

    6 32726 3272 93 -3 0

    7 32727 3272 91 -3 0

    8 32728 3272 89 -3 1

    9 32729 3272 87 -2 1

    10 32730 3273 85 -2 1

    11 32731 3273 83 -2 1

    12 32732 3273 81 -2 1

    13 32733 3273 79 -2 1

    14 32734 3273 77 -2 1

    15 32735 3273 75 -2 1

    16 32736 3273 73 -2 1

    17 32737 3273 71 -2 1

    18 32738 3273 69 -2 2

    19 32739 3273 67 -1 2

    20 32740 3274 65 -1 2

    21 32741 3274 63 -1 2

    22 32742 3274 61 -1 2

    23 32743 3274 59 -1 2

    24 32744 3274 57 -1 2

    25 32745 3274 55 -1 2

    26 32746 3274 53 -1 2

    27 32747 3274 51 -1 2

    28 32748 3274 49 -1 3

    29 32749 3274 47 0 3

    30 32750 3275 45 0 3

    31 32751 3275 43 0 3

    32 32752 3275 41 0 3

    33 ! 32753 3275 39 0 3

    34 " 32754 3275 37 0 3

    35 # 32755 3275 35 0 3

    36 $ 32756 3275 33 0 3

    37 % 32757 3275 31 0 3

    38 & 32758 3275 29 0 4

    39 ' 32759 3275 27 0 4

    40 ( 32760 3276 25 0 4

    41 ) 32761 3276 23 0 4

    42 * 32762 3276 21 0 4

    43 + 32763 3276 19 0 4

    44 , 32764 3276 17 0 4

    45 - 32765 3276 15 0 4

    46 . 32766 3276 13 0 4

    47 / 32767 3276 11 0 4

    48 0 0 0 9 1 5

    49 1 1 0 9 1 5

    50 2 2 0 9 1 5

    51 3 3 0 9 1 5

    52 4 4 0 9 1 5

    53 5 5 0 9 1 5

    54 6 6 0 9 1 5

    55 7 7 0 9 1 5

    56 8 8 0 9 1 5

    57 9 9 0 9 1 5

    58 : 10 1 11 2 6

    59 ; 11 1 13 2 6

    60 < 12 1 15 2 6

    61 = 13 1 17 2 6

    62 > 14 1 19 2 6

    63 ? 15 1 21 2 6

    64 @ 16 1 23 2 6

    65 A 17 1 25 2 6

    66 B 18 1 27 2 6

    67 C 19 1 29 2 6

    68 D 20 2 31 3 7

    69 E 21 2 33 3 7

    70 F 22 2 35 3 7

    71 G 23 2 37 3 7

    72 H 24 2 39 3 7

    73 I 25 2 41 3 7

    74 J 26 2 43 3 7

    75 K 27 2 45 3 7

    76 L 28 2 47 3 7

    77 M 29 2 49 3 7

    78 N 30 3 51 4 8

    79 O 31 3 53 4 8

    80 P 32 3 55 4 8

    81 Q 33 3 57 4 8

    82 R 34 3 59 4 8

    83 S 35 3 61 4 8

    84 T 36 3 63 4 8

    85 U 37 3 65 4 8

    86 V 38 3 67 4 8

    87 W 39 3 69 4 8

    88 X 40 4 71 5 9

    89 Y 41 4 73 5 9

    90 Z 42 4 75 5 9

    91 [ 43 4 77 5 9

    92 \ 44 4 79 5 9

    93 ] 45 4 81 5 9

    94 ^ 46 4 83 5 9

    95 _ 47 4 85 5 9

    96 ` 48 4 87 5 9

    97 a 49 4 89 5 9

    98 b 50 5 91 6 10

    99 c 51 5 93 6 10

    100 d 52 5 95 6 10

    101 e 53 5 97 6 10

    102 f 54 5 99 6 10

    103 g 55 5 101 6 10

    104 h 56 5 103 6 10

    105 i 57 5 105 6 10

    106 j 58 5 107 6 10

    107 k 59 5 109 6 10

    108 l 60 6 111 7 11

    109 m 61 6 113 7 11

    110 n 62 6 115 7 11

    111 o 63 6 117 7 11

    112 p 64 6 119 7 11

    113 q 65 6 121 7 11

    114 r 66 6 123 7 11

    115 s 67 6 125 7 11

    116 t 68 6 127 7 11

    117 u 69 6 129 7 11

    118 v 70 7 131 8 12

    119 w 71 7 133 8 12

    120 x 72 7 135 8 12

    121 y 73 7 137 8 12

    122 z 74 7 139 8 12

    123 { 75 7 141 8 12

    124 | 76 7 143 8 12

    125 } 77 7 145 8 12

    126 ~ 78 7 147 8 12

    127  79 7 149 8 12

    128 € 80 8 151 9 13

    129  81 8 153 9 13

    130 ‚ 82 8 155 9 13

    131 ƒ 83 8 157 9 13

    132 „ 84 8 159 9 13

    133 … 85 8 161 9 13

    134 † 86 8 163 9 13

    135 ‡ 87 8 165 9 13

    136 ˆ 88 8 167 9 13

    137 ‰ 89 8 169 9 13

    138 Š 90 9 171 10 14

    139 ‹ 91 9 173 10 14

    140 Π92 9 175 10 14

    141  93 9 177 10 14

    142 Ž 94 9 179 10 14

    143  95 9 181 10 14

    144  96 9 183 10 14

    145 ‘ 97 9 185 10 14

    146 ’ 98 9 187 10 14

    147 “ 99 9 189 10 14

    148 ” 100 10 191 11 15

    149 • 101 10 193 11 15

    150 – 102 10 195 11 15

    151 — 103 10 197 11 15

    152 ˜ 104 10 199 11 15

    153 ™ 105 10 201 11 15

    154 š 106 10 203 11 15

    155 › 107 10 205 11 15

    156 œ 108 10 207 11 15

    157  109 10 209 11 15

    158 ž 110 11 211 12 16

    159 Ÿ 111 11 213 12 16

    160   112 11 215 12 16

    161 ¡ 113 11 217 12 16

    162 ¢ 114 11 219 12 16

    163 £ 115 11 221 12 16

    164 ¤ 116 11 223 12 16

    165 ¥ 117 11 225 12 16

    166 ¦ 118 11 227 12 16

    167 § 119 11 229 12 16

    168 ¨ 120 12 231 13 17

    169 © 121 12 233 13 17

    170 ª 122 12 235 13 17

    171 « 123 12 237 13 17

    172 ¬ 124 12 239 13 17

    173 ­ 125 12 241 13 17

    174 ® 126 12 243 13 17

    175 ¯ 127 12 245 13 17

    176 ° 128 12 247 13 17

    177 ± 129 12 249 13 17

    178 ² 130 13 251 14 18

    179 ³ 131 13 253 14 18

    180 ´ 132 13 255 14 18

    181 µ 133 13 257 14 18

    182 ¶ 134 13 259 14 18

    183 · 135 13 261 14 18

    184 ¸ 136 13 263 14 18

    185 ¹ 137 13 265 14 18

    186 º 138 13 267 14 18

    187 » 139 13 269 14 18

    188 ¼ 140 14 271 15 19

    189 ½ 141 14 273 15 19

    190 ¾ 142 14 275 15 19

    191 ¿ 143 14 277 15 19

    192 À 144 14 279 15 19

    193 Á 145 14 281 15 19

    194 Â 146 14 283 15 19

    195 Ã 147 14 285 15 19

    196 Ä 148 14 287 15 19

    197 Å 149 14 289 15 19

    198 Æ 150 15 291 16 20

    199 Ç 151 15 293 16 20

    200 È 152 15 295 16 20

    201 É 153 15 297 16 20

    202 Ê 154 15 299 16 20

    203 Ë 155 15 301 16 20

    204 Ì 156 15 303 16 20

    205 Í 157 15 305 16 20

    206 Î 158 15 307 16 20

    207 Ï 159 15 309 16 20

    208 Ð 160 16 311 17 21

    209 Ñ 161 16 313 17 21

    210 Ò 162 16 315 17 21

    211 Ó 163 16 317 17 21

    212 Ô 164 16 319 17 21

    213 Õ 165 16 321 17 21

    214 Ö 166 16 323 17 21

    215 × 167 16 325 17 21

    216 Ø 168 16 327 17 21

    217 Ù 169 16 329 17 21

    218 Ú 170 17 331 18 22

    219 Û 171 17 333 18 22

    220 Ü 172 17 335 18 22

    221 Ý 173 17 337 18 22

    222 Þ 174 17 339 18 22

    223 ß 175 17 341 18 22

    224 à 176 17 343 18 22

    225 á 177 17 345 18 22

    226 â 178 17 347 18 22

    227 ã 179 17 349 18 22

    228 ä 180 18 351 19 23

    229 å 181 18 353 19 23

    230 æ 182 18 355 19 23

    231 ç 183 18 357 19 23

    232 è 184 18 359 19 23

    233 é 185 18 361 19 23

    234 ê 186 18 363 19 23

    235 ë 187 18 365 19 23

    236 ì 188 18 367 19 23

    237 í 189 18 369 19 23

    238 î 190 19 371 20 24

    239 ï 191 19 373 20 24

    240 ð 192 19 375 20 24

    241 ñ 193 19 377 20 24

    242 ò 194 19 379 20 24

    243 ó 195 19 381 20 24

    244 ô 196 19 383 20 24

    245 õ 197 19 385 20 24

    246 ö 198 19 387 20 24

    247 ÷ 199 19 389 20 24

    248 ø 200 20 391 21 25

    249 ù 201 20 393 21 25

    250 ú 202 20 395 21 25

    251 û 203 20 397 21 25

    252 ü 204 20 399 21 25

    253 ý 205 20 401 21 25

    254 þ 206 20 403 21 25

    255 ÿ 207 20 405 21 25

    Timing of different operators, functions and combinations thereof, gives only an indication though

    USE tempdb;

    GO

    SET NOCOUNT ON;

    DECLARE @START_NUM INT = 48;

    DECLARE @RANGE INT = 10;

    DECLARE @SAMPLE_SIZE INT = 10000000;

    DECLARE @SAMPLE_DISTR INT = 256;

    DECLARE @PATTERN VARCHAR(50) = '%[^0-9]%';

    DECLARE @RV_PATTERN VARCHAR(50) = '%[0-9]%';

    IF OBJECT_ID('dbo.TBL_TEST_SET') IS NULL

    BEGIN

    -- DROP TABLE dbo.TBL_TEST_SET;

    CREATE TABLE dbo.TBL_TEST_SET

    (

    TS_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_SET_TS_ID PRIMARY KEY CLUSTERED WITH ( FILLFACTOR = 100, DATA_COMPRESSION = PAGE)

    ,TS_CHAR CHAR(1) NOT NULL

    )

    ;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 (@SAMPLE_SIZE) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 a,E1 b,E1 c,E1 d,E1 e,E1 f,E1 g,E1 h,E1 i)

    INSERT INTO dbo.TBL_TEST_SET(TS_CHAR)

    SELECT

    CHAR(T.N % @SAMPLE_DISTR)

    FROM Tally T;

    END

    DECLARE @INT_BUCKET INT = 0;

    DECLARE @CHR_BUCKET CHAR(1) = '';

    DECLARE @TIMING TABLE

    (

    TIMING_ID INT IDENTITY(1,1) NOT NULL

    ,TIMING_TD DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())

    ,TIMING_TXT VARCHAR(100) NOT NULL

    );

    /* BASE LINE */

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = T.TS_CHAR');

    SELECT

    @CHR_BUCKET = T.TS_CHAR

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = T.TS_CHAR');

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR)');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR)');

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = T.TS_CHAR #2');

    SELECT

    @CHR_BUCKET = T.TS_CHAR

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = T.TS_CHAR #2');

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) #2');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) #2');

    /* CASE #1*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 ELSE 0 END');

    SELECT

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 ELSE 0 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 ELSE 0 END');

    /* CASE #2*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 END');

    /* CASE #3*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) BETWEEN 48 AND 57 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) BETWEEN 48 AND 57 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) BETWEEN 48 AND 57 THEN 1 END');

    /* CASE #2*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) < 10 THEN 1 ELSE 0 END');

    SELECT

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) < 10 THEN 1 ELSE 0 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) < 10 THEN 1 ELSE 0 END');

    /* CASE #3*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) > 10 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) > 10 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) > 10 THEN 1 END');

    /* BITWISE AND #1*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7F');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7F

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7F');

    /* BITWISE AND #2*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFF');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFF

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFF');

    /* BITWISE AND #3*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFF');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFF

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFF');

    /* BITWISE AND #4*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFFFF');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFFFF

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFFFF');

    /* SUBTRACTION #1*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 1');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) - 1

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 1');

    /* SUBTRACTION #10*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 10');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) - 10

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 10');

    /* SUBTRACTION #48*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 48');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) - 48

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 48');

    /* SUBTRACTION #100*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 100');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) - 100

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 100');

    /* SUBTRACTION #1000*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 1000');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) - 1000

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - 1000');

    /* SUBTRACTION #@START_NUM*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - @START_NUM');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) - @START_NUM

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) - @START_NUM');

    /* MODULO #10*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) % 10');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) % 10

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) % 10');

    /* DIVISION #10*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) / 10');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) / 10

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) / 10');

    /* MODULO #100*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) % 100');

    SELECT

    @INT_BUCKET = ASCII(T.TS_CHAR) % 100

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ASCII(T.TS_CHAR) % 100');

    /* SUBTRACTION #48 MOD 0x7FFF*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFF');

    SELECT

    @INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFF

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFF');

    /* SUBTRACTION #48 MOD 0x7FFF*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF');

    SELECT

    @INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF');

    /* SUBTRACTION CASE #48 MOD 0x7FFF*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ((ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF) < 10 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN ((ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF) < 10 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN ((ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF) < 10 THEN 1 END');

    /* CASE BETWEEN #1*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = CASE WHEN T.TS_CHAR BETWEEN ''0'' AND ''9'' THEN 1 END');

    SELECT

    @CHR_BUCKET = CASE WHEN T.TS_CHAR BETWEEN '0' AND '9' THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = CASE WHEN T.TS_CHAR BETWEEN ''0'' AND ''9'' THEN 1 END');

    /* CASE BETWEEN #2*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN BETWEEN ''0'' AND ''9'' THEN 1 END');

    SELECT

    @CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN BETWEEN '0' AND '9' THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN BETWEEN ''0'' AND ''9'' THEN 1 END');

    /* CASE BETWEEN #3*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN2 BETWEEN ''0'' AND ''9'' THEN 1 END');

    SELECT

    @CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN2 BETWEEN '0' AND '9' THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN2 BETWEEN ''0'' AND ''9'' THEN 1 END');

    /* PATINDEX #1*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN)');

    SELECT

    @INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN)');

    /* PATINDEX #2*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2)');

    SELECT

    @INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2)');

    /* PATINDEX #3*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN)');

    SELECT

    @INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN)');

    /* PATINDEX #4*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@@RV_PATTERN,SUBSTRING(T.TS_CHAR COLLATE Latin1_General_BIN2,N,1))');

    SELECT

    @INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@@RV_PATTERN,SUBSTRING(T.TS_CHAR COLLATE Latin1_General_BIN2,N,1))');

    /* PATINDEX #5*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR)');

    SELECT

    @INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR)');

    /* CASE PATINDEX #1*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END');

    /* CASE PATINDEX #2*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END');

    /* CASE PATINDEX #3*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END');

    /* CASE PATINDEX #4*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END');

    /* CASE PATINDEX #5*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR) = 0 THEN 1 END');

    SELECT

    @INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR) = 0 THEN 1 END

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR) = 0 THEN 1 END');

    /* ARITHMETIC IDENTIFICATION #1*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ((ASCII(T.TS_CHAR) - 38) / 10 )');

    SELECT

    @INT_BUCKET = ((ASCII(T.TS_CHAR) - 38) / 10 )

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ((ASCII(T.TS_CHAR) - 38) / 10 )');

    /* ARITHMETIC IDENTIFICATION #2*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ABS(ASCII(T.TS_CHAR) - 48) + ABS(ASCII(T.TS_CHAR) - 57)');

    SELECT

    @INT_BUCKET = ABS(ASCII(T.TS_CHAR) - 48) + ABS(ASCII(T.TS_CHAR) - 57)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = ABS(ASCII(T.TS_CHAR) - 48) + ABS(ASCII(T.TS_CHAR) - 57)');

    /* ARITHMETIC IDENTIFICATION #3*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (((ASCII(T.TS_CHAR) - @START_NUM) & 0x7FFF) / 10)');

    SELECT

    @INT_BUCKET = (((ASCII(T.TS_CHAR) - @START_NUM) & 0x7FFF) / 10)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (((ASCII(T.TS_CHAR) - @START_NUM) & 0x7FFF) / 10)');

    /* ARITHMETIC IDENTIFICATION #4*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (((ASCII(T.TS_CHAR) - 48) & 0x7FFF) / 10)');

    SELECT

    @INT_BUCKET = (((ASCII(T.TS_CHAR) - 48) & 0x7FFF) / 10)

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (((ASCII(T.TS_CHAR) - 48) & 0x7FFF) / 10)');

    /* ARITHMETIC IDENTIFICATION #5*/

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (ASCII(T.TS_CHAR) + 2) / 10');

    SELECT

    @INT_BUCKET = (ASCII(T.TS_CHAR) + 2) / 10

    FROM TBL_TEST_SET T

    INSERT INTO @TIMING(TIMING_TXT) VALUES('@INT_BUCKET = (ASCII(T.TS_CHAR) + 2) / 10');

    SELECT

    T.TIMING_TXT

    ,@SAMPLE_SIZE AS SAMPLE_SIZE

    ,@SAMPLE_DISTR AS SAMPLE_DISTR

    ,DATEDIFF(MICROSECOND,MIN(T.TIMING_TD),MAX(T.TIMING_TD)) / 1000.0 AS DURATION_MSEC

    FROM @TIMING T

    GROUP BY T.TIMING_TXT

    ORDER BY (DATEDIFF(MICROSECOND,MIN(T.TIMING_TD),MAX(T.TIMING_TD)))

    Timing results

    TIMING_TXT SAMPLE_SIZE SAMPLE_DISTR DURATION_MSEC

    ---------------------------------------------------------------------------------------------------- ----------- ------------ ---------------------------------------

    @CHR_BUCKET = T.TS_CHAR #2 10000000 256 1532.803000

    @CHR_BUCKET = T.TS_CHAR 10000000 256 1546.402000

    @INT_BUCKET = ASCII(T.TS_CHAR) 10000000 256 1626.403000

    @INT_BUCKET = ASCII(T.TS_CHAR) #2 10000000 256 1640.003000

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7F 10000000 256 1702.403000

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFFFF 10000000 256 1705.404000

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFF 10000000 256 1710.404000

    @INT_BUCKET = ASCII(T.TS_CHAR) - 10 10000000 256 1720.004000

    @INT_BUCKET = ASCII(T.TS_CHAR) % 100 10000000 256 1733.603000

    @INT_BUCKET = ASCII(T.TS_CHAR) - 48 10000000 256 1735.004000

    @INT_BUCKET = ASCII(T.TS_CHAR) % 10 10000000 256 1735.603000

    @INT_BUCKET = ASCII(T.TS_CHAR) - 1 10000000 256 1742.603000

    @INT_BUCKET = ASCII(T.TS_CHAR) - 1000 10000000 256 1749.203000

    @INT_BUCKET = ASCII(T.TS_CHAR) / 10 10000000 256 1751.203000

    @INT_BUCKET = ASCII(T.TS_CHAR) & 0x7FFFFF 10000000 256 1753.203000

    @INT_BUCKET = ASCII(T.TS_CHAR) - 100 10000000 256 1769.803000

    @INT_BUCKET = ASCII(T.TS_CHAR) - @START_NUM 10000000 256 1782.404000

    @INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFF 10000000 256 1813.604000

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 END 10000000 256 1829.203000

    @INT_BUCKET = (ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF 10000000 256 1844.803000

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) < 10 THEN 1 ELSE 0 END 10000000 256 1844.804000

    @INT_BUCKET = (ASCII(T.TS_CHAR) + 2) / 10 10000000 256 1859.403000

    @INT_BUCKET = ((ASCII(T.TS_CHAR) - 38) / 10 ) 10000000 256 1866.403000

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) > 10 THEN 1 END 10000000 256 1876.003000

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) = 1 THEN 1 ELSE 0 END 10000000 256 1876.004000

    @INT_BUCKET = (((ASCII(T.TS_CHAR) - 48) & 0x7FFF) / 10) 10000000 256 2078.804000

    @INT_BUCKET = CASE WHEN ASCII(T.TS_CHAR) BETWEEN 48 AND 57 THEN 1 END 10000000 256 2079.804000

    @INT_BUCKET = (((ASCII(T.TS_CHAR) - @START_NUM) & 0x7FFF) / 10) 10000000 256 2129.005000

    @INT_BUCKET = CASE WHEN ((ASCII(T.TS_CHAR) - 48) & 0x7FFFFFFF) < 10 THEN 1 END 10000000 256 2250.404000

    @INT_BUCKET = ABS(ASCII(T.TS_CHAR) - 48) + ABS(ASCII(T.TS_CHAR) - 57) 10000000 256 2251.405000

    @INT_BUCKET = PATINDEX(@@RV_PATTERN,SUBSTRING(T.TS_CHAR COLLATE Latin1_General_BIN2,N,1)) 10000000 256 2876.405000

    @INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) 10000000 256 2887.807000

    @INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) 10000000 256 2910.606000

    @INT_BUCKET = PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) 10000000 256 2927.205000

    @CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN BETWEEN '0' AND '9' THEN 1 END 10000000 256 3379.007000

    @CHR_BUCKET = CASE WHEN T.TS_CHAR COLLATE Latin1_General_BIN2 BETWEEN '0' AND '9' THEN 1 END 10000000 256 3395.207000

    @INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END 10000000 256 3440.007000

    @INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END 10000000 256 3453.606000

    @INT_BUCKET = CASE WHEN PATINDEX(@PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN) = 0 THEN 1 END 10000000 256 3460.607000

    @INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR COLLATE Latin1_General_BIN2) = 0 THEN 1 END 10000000 256 3471.206000

    @CHR_BUCKET = CASE WHEN T.TS_CHAR BETWEEN '0' AND '9' THEN 1 END 10000000 256 4021.411000

    @INT_BUCKET = PATINDEX(@RV_PATTERN,T.TS_CHAR) 10000000 256 5740.211000

    @INT_BUCKET = CASE WHEN PATINDEX(@RV_PATTERN,T.TS_CHAR) = 0 THEN 1 END 10000000 256 6080.412000

    Edit: type and highlight.

  • Jeff Moden (10/30/2014)


    Now we're cooking with gas! Well done! I had eye surgery today (lens replacement) and can't see so well yet and can't spend much time in front of the computer but I'll run some additional tests over the weekend using variable length data and see what happens.

    We also need to try the same thing on Alan's good pattern matching code.

    As Eirikur suggests, this is an example of the SQL Server Community (same initials as SQL Server Central :-)) at it's best. Keep 'em coming folks!

    BTW, that's one heck of a nice machine you have there, Eirikur!

    Good luck with the eye Jeff, hope every thing goes well.

    😎

    ...yes the machine is a nifty little thingy, quad core xeon, 8Mb cache, makes a nice rumble;-)

  • Jeff Moden (10/30/2014)


    We also need to try the same thing on Alan's good pattern matching code.

    Sorry for not checking back sooner - I spent the weekend dealing with some <sarcasm>very realistic and reasonable deadlines </sarcasm>.

    This is the best I could do so far:

    ALTER FUNCTION dbo.PatExclude8K

    (

    @String VARCHAR(8000),

    @Pattern VARCHAR(50)

    )

    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)),

    Itally(N) AS

    (

    SELECT TOP(CONVERT(BIGINT,LEN(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E1 T1 CROSS JOIN E1 T2 CROSS JOIN E1 T3 CROSS JOIN E1 T4

    )

    SELECT

    (

    SELECT CASE WHEN PATINDEX(@Pattern,SUBSTRING(@String COLLATE Latin1_General_BIN,N,1)) = 0 THEN SUBSTRING(@String,N,1) END

    FROM iTally

    FOR XML PATH('')

    ) AS NewString;

    GO

    This get's me about a 15-20% improvement compared to the previous version:

    (note: for brevity, I attached the old and new version with the test script)

    Beginning execution loop

    ========== Using PatExclude8K_old ==========

    2066

    ========== Using PatExclude8K_old ==========

    2140

    ========== Using PatExclude8K_old ==========

    2153

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Using PatExclude8K ==========

    1603

    ========== Using PatExclude8K ==========

    1620

    ========== Using PatExclude8K ==========

    1660

    Batch execution completed 3 times.

    I don't know how I could refactor this:

    WHERE ASCII(SUBSTRING(@pString,N,1)) BETWEEN 48 AND 57

    to take a pattern... :crazy:

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Adding a little bit of tinkering based on the operator cost results in the previous post, mainly changing from the CASE to a WHERE clause for filtering. Not a big gain but some.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID('temp1.dbo.PatEExclude8K') IS NOT NULL DROP FUNCTION dbo.PatEExclude8K;

    IF OBJECT_ID('temp1.dbo.PatExclude8K') IS NOT NULL DROP FUNCTION dbo.PatExclude8K;

    GO

    CREATE FUNCTION dbo.PatEExclude8K

    (

    @String VARCHAR(8000),

    @Pattern VARCHAR(50)

    )

    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)),

    Itally(N) AS

    (

    SELECT TOP(CONVERT(INT,LEN(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E1 T1 CROSS JOIN E1 T2 CROSS JOIN E1 T3 CROSS JOIN E1 T4

    )

    SELECT

    (

    SELECT SUBSTRING(@String,N,1)

    FROM iTally

    WHERE 0 = PATINDEX(@Pattern,SUBSTRING(@String COLLATE Latin1_General_BIN,N,1))

    FOR XML PATH('')

    ) AS NewString;

    GO

    CREATE FUNCTION dbo.PatExclude8K

    (

    @String VARCHAR(8000),

    @Pattern VARCHAR(50)

    )

    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)),

    Itally(N) AS

    (

    SELECT TOP(CONVERT(BIGINT,LEN(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E1 T1 CROSS JOIN E1 T2 CROSS JOIN E1 T3 CROSS JOIN E1 T4

    )

    SELECT

    (

    SELECT CASE WHEN PATINDEX(@Pattern,SUBSTRING(@String COLLATE Latin1_General_BIN,N,1)) = 0 THEN SUBSTRING(@String,N,1) END

    FROM iTally

    FOR XML PATH('')

    ) AS NewString;

    GO

    -- make sure the new version works

    --SELECT * FROM dbo.PatExclude8K('123acb456!','[^0-9!]')

    --SELECT * FROM dbo.PatEExclude8K('123acb456!','[^0-9!]')

    --===== Create the 100K row test table

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

    SELECT TOP 100000

    txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')

    INTO #val

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2;

    ALTER TABLE #Val

    ADD PRIMARY KEY CLUSTERED (txt);

    --===== Do the tests. Had to use duration because one

    -- of the tests is on the new scalar function and

    -- SET STATISTICS doesn't report on those correctly.

    GO

    PRINT '========== Using PatEExclude8K ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME;

    SELECT @StartTime = GETDATE();

    SELECT @string = newstring

    FROM #val

    CROSS APPLY dbo.PatEExclude8K(txt, '[^0-9]');

    PRINT DATEDIFF(ms,@StartTime,GETDATE());

    GO 3

    PRINT '========== Using PatExclude8K ==========';

    DECLARE @String VARCHAR(36)

    ,@StartTime DATETIME;

    SELECT @StartTime = GETDATE();

    SELECT @string = newstring

    FROM #val

    CROSS APPLY dbo.PatExclude8K(txt, '[^0-9]');

    PRINT DATEDIFF(ms,@StartTime,GETDATE());

    GO 3

    Results

    Beginning execution loop

    ========== Using PatEExclude8K ==========

    1450

    ========== Using PatEExclude8K ==========

    1470

    ========== Using PatEExclude8K ==========

    1496

    Batch execution completed 3 times.

    Beginning execution loop

    ========== Using PatExclude8K ==========

    1586

    ========== Using PatExclude8K ==========

    1563

    ========== Using PatExclude8K ==========

    1556

    Batch execution completed 3 times.

  • Just dawned on me, kind of a deja vu, here is around six years old thread on the same subject.

    😎

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply