Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

August 2008 Blog Posts

Simple FAQ search algorithm

-- Prepare sample data DECLARE       @Keywords TABLE        (               FaqID INT,               Keyword VARCHAR(200)        ) INSERT @Keywords SELECT 1, 'help' UNION ALL SELECT 1, 'resolve' UNION ALL SELECT 1, 'issue' UNION ALL ...

posted @ Wednesday, August 13, 2008 4:27 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Extract UK postcode

CREATE FUNCTION dbo.fnExtractPostCodeUK (        @Data VARCHAR(8000) ) RETURNS VARCHAR(8) AS BEGIN         RETURN        COALESCE(                            -- AANN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),                            -- AANA NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 8),                            -- ANN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            -- AAN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            -- ANA NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @Data + ' '), 0), 7),                            --   AN NAA                            SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' '...

posted @ Wednesday, August 13, 2008 2:32 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Validate UK postcode

CREATE FUNCTION dbo.fnValidatePostCodeUK (        @PostCode VARCHAR(8) ) RETURNS BIT AS BEGIN        RETURN CASE                      --   Special case GIR 0AA                      WHEN @PostCode LIKE 'GIR 0AA' THEN 1                      -- Current postcode prefixes                      WHEN   LEFT(@Postcode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP',...

posted @ Wednesday, August 13, 2008 1:46 PM | Feedback (7) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to sum up an unknown number of records

-- Initialize the search parameter DECLARE       @WantedValue INT   SET    @WantedValue = 221   -- Stage the source data DECLARE       @Data TABLE        (               RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,               MaxItems INT,               CurrentItems INT DEFAULT 0,               FaceValue INT,               BestUnder INT DEFAULT 0,               BestOver INT DEFAULT 1        )   -- Aggregate the source data INSERT        @Data               (                      MaxItems,                      FaceValue               ) SELECT        COUNT(*),               Qty FROM          (                      SELECT 899 AS Qty UNION ALL                      SELECT 100 UNION ALL                      SELECT 95 UNION ALL                      SELECT 50 UNION ALL                      SELECT 55 UNION ALL                      SELECT 40 UNION ALL                      SELECT 5 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                      SELECT 100 UNION ALL                     ...

posted @ Tuesday, August 12, 2008 5:06 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Another sequencing algorithm

This problem originated here http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y and I post the solution here for two reasons. 1) The forum above does not support code tags 2) The common interest is high enough -- Prepare sample data DECLARE @Sample TABLE         (              HoleID CHAR(8),              mFrom SMALLMONEY,              mTo SMALLMONEY,              Result SMALLMONEY,              PRIMARY KEY CLUSTERED              (                  HoleID,                  mFrom              ),              Seq INT          ) INSERT  @Sample          (              HoleID,              mFrom,              mTo,              Result          ) SELECT  'TWDD0004',   1   ,   2   ,  0.86 UNION ALL SELECT  'TWDD0004',   3   ,   4   ,  8.93 UNION ALL SELECT  'TWDD0004',   4   ,   5   ,  2.78 UNION ALL SELECT  'TWDD0004',   8   ,  ...

posted @ Tuesday, August 12, 2008 2:24 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

How to tell if a number is a "POWER of 2"-number

SELECT  Number,         1 - SIGN(Number & (Number - 1)) FROM    master..spt_values WHERE   Type = 'P'         AND Number > 0   Other way is   CREATE FUNCTION dbo.isPowerOf2 (       @i INT ) RETURNS BIT AS BEGIN       DECLARE @x FLOAT          SET @x = LOG(Number) / LOG(2)       RETURN      CASE                   WHEN FLOOR(@x) = CEILING(@x) THEN 1                   ELSE 0             END END

posted @ Saturday, August 09, 2008 1:22 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Powered by:
Powered By Subtext Powered By ASP.NET