DECLARE@s VARCHAR(100) SET@s = 'aardddvaaaarrkkkk' -- Highest frequency of same character SELECTTOP 1 WITH TIES [char], COUNT(*) AS cnt FROM ( SELECT SUBSTRING(@s, 1 + Number, 1) [char] FROM master.
Read more →
SELECTnumber, CASE WHEN number % 100 IN (11, 12, 13) THEN 'th' WHEN number % 10 = 1 THEN 'st' WHEN number % 10 = 2 THEN 'nd' WHEN number % 10 = 3 THEN 'rd' ELSE 'th' END AS Ordinal FROMmaster.
Read more →
Today I helped a guy out with a special request. His goal was to remove all records that are subsets or parts of another record, a superset record. See this sample data
Read more →
Today I had to write some code to dynamically get the job name currently running.
DECLARE@SQL NVARCHAR(72), @jobID UNIQUEIDENTIFIER, @jobName SYSNAME SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)' EXECsp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT SELECT @jobName = name FROM msdb.
Read more →
This is a piece of code I use to create a resultset from and display in Outlook calendar.
CREATEPROCEDURE dbo.uspGetScheduleTimes ( @startDate DATETIME, @endDate DATETIME ) AS /* This code is blogged here http://weblogs.
Read more →
DECLARE @a TABLE (pk INT) DECLARE @b TABLE (fk INT, i INT) DECLARE @c TABLE (fk INT, j INT)
INSERT @a SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
Read more →
CREATE PROCEDURE dbo.uspCreatePassword ( @UpperCaseItems SMALLINT, @LowerCaseItems SMALLINT, @NumberItems SMALLINT, @SpecialItems SMALLINT ) AS SET NOCOUNT ON – Initialize some variables DECLARE @UpperCase VARCHAR(26), @LowerCase VARCHAR(26), @Numbers VARCHAR(10), @Special VARCHAR(13), @Temp VARCHAR(8000), @Password VARCHAR(8000), @i SMALLINT, @c VARCHAR(1), @v TINYINT – Set the default items in each group of characters SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', @LowerCase = 'abcdefghijklmnopqrstuvwxyz', @Numbers = '0123456789', @Special = '!
Read more →
-- Prepare sample data DECLARE@Keywords TABLE ( FaqID INT, Keyword VARCHAR(200) ) INSERT@Keywords SELECT1, 'help' UNION ALL SELECT1, 'resolve' UNION ALL SELECT1, 'issue' UNION ALL SELECT2, 'Red Herring' DECLARE@Faq TABLE ( FaqID INT, Question VARCHAR(MAX), Answer VARCHAR(MAX) ) INSERT@Faq SELECT1, 'This is stupid question', 'This is a stupid answer' DECLARE@Question AS VARCHAR(200) -- Prepare user supplied parameter SET@Question = 'How can we help resolve your issue' -- Show the expected result SELECTf.
Read more →
CREATEFUNCTION dbo.fnExtractPostCodeUK ( @Data VARCHAR(8000) ) RETURNSVARCHAR(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] %', ' ' + @Data + ' '), 0), 6), -- Special case GIR 0AA SUBSTRING(@Data, NULLIF(PATINDEX('% GIR 0AA %', ' ' + @Data + ' '), 0), 7) ) END Here are some sample data to play with declare@TestTab Table (postcode varchar(50) not null) Insert@TestTab values('SK13 8LY') --Valid Insert@TestTab values('M1 1AA') --Valid Insert@TestTab values('M60 1NW') --Valid Insert@TestTab values('GIR 0AA') --Valid Insert@TestTab values('CR2 6XH') --Valid Insert@TestTab values('DN55 1PT') --Valid Insert@TestTab values('W1A 1HQ') --Valid Insert@TestTab values('EC1A 1BB') --Valid Insert@TestTab values('India') --Invalid Insert@TestTab values('12345') --Invalid Insert@TestTab values('Glossop SK13 8LY') --Invalid but contains a valid postcode INSERT@TestTab VALUES('XA1 1AA') --WHAT WILL THE FUNCTION SAY ABOUT THIS?
Read more →
CREATEFUNCTION dbo.fnValidatePostCodeUK ( @PostCode VARCHAR(8) ) RETURNSBIT AS BEGIN RETURN CASE -- Special case GIR 0AA WHEN @PostCode LIKE 'GIR 0AA' THEN 1 -- Current postcode prefixes WHENLEFT(@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', 'NR', 'NW', 'OL', 'OX', 'PA', 'PE', 'PH', 'PL', 'PO', 'PR', 'RG', 'RH', 'RM', 'SA', 'SE', 'SG', 'SK', 'SL', 'SM', 'SN', 'SO', 'SP', 'SR', 'SS', 'ST', 'SW', 'SY', 'TA', 'TD', 'TF', 'TN', 'TQ', 'TR', 'TS', 'TW', 'UB', 'WA', 'WC', 'WD', 'WF', 'WN', 'WR', 'WS', 'WV', 'YO', 'ZE') OR WHEN LEFT(@Postcode, 1) NOT IN('B', 'E', 'G', 'L', 'N', 'S', 'W') THEN 0 -- AANN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- AANA NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- ANN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- AAN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- ANA NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- AN NAA WHEN @PostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1 -- Not a valid postcode ELSE 0 END END Updated to reflect current valid postcodes only.
Read more →
– 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 ) SELECTCOUNT(), Qty FROM( SELECT 899 AS Qty UNIONALL SELECT 100 UNION ALL SELECT 95 UNIONALL 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 SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 250 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 350 UNION ALL SELECT 450 UNION ALL SELECT 450 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 1 UNION ALL SELECT 10 UNION ALL SELECT 1 ) AS d GROUPBY Qty ORDERBY Qty DESC – Declare some control variables DECLARE@CurrentSum INT, @BestUnder INT, @BestOver INT, @RecID INT – If exact single wanted sum, select that item!
Read more →
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.
Read more →
SELECT Number, 1 - SIGN(Number & (Number - 1)) FROM master..spt_values WHERE Type = 'P' AND Number > 0
Other way is
CREATEFUNCTION dbo.isPowerOf2 ( @i INT ) RETURNSBIT AS BEGIN DECLARE @x FLOAT SET @x = LOG(Number) / LOG(2) RETURN CASE WHEN FLOOR(@x) = CEILING(@x) THEN 1 ELSE 0 END END
Legacy Comments
RamiReddy
2008-08-13
re: How to tell if a number is a "POWER of 2"-number Is that first query will work for the number 524288 which is 2 power 19.
Read more →
Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH. Number
Binary digits
VARBINARY
LEN
DATALENGTH
32
Read more →
When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS. Here are some links to remedy the problems http://msdn.
Read more →
CREATEFUNCTION dbo.fnGetSQLServerAuthenticationMode ( ) RETURNSINT AS BEGIN DECLARE @InstanceName NVARCHAR(1000), @Key NVARCHAR(4000), @LoginMode INT EXEC master..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', @InstanceName OUTPUT IF @@ERROR <> 0 OR @InstanceName IS NULL RETURN NULL SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\' EXEC master.
Read more →
CREATEPROCEDURE dbo.uspSetSQLServerAuthenticationMode ( @MixedMode BIT ) AS SETNOCOUNT ON DECLARE@InstanceName NVARCHAR(1000), @Key NVARCHAR(4000), @NewLoginMode INT, @OldLoginMode INT EXECmaster..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', @InstanceName OUTPUT IF@@ERROR <> 0 OR @InstanceName IS NULL BEGIN RAISERROR('Could not read SQL Server instance name.
Read more →
DECLARE@Stage TABLE ( RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Data VARCHAR(90), Section INT ) INSERT@Stage ( Data ) EXECxp_cmdshell 'ipconfig /all' DECLARE@Section INT SET@Section = 0 UPDATE @Stage SET@Section = Section = CASE WHENASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE@Section END SELECTMAX(CASE WHEN x.
Read more →
For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below. Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.
Read more →
The last two days I have been involved in a rather interesting discussion. The original poster wanted a fast way to get missing date ranges in a series of date pairs.
Read more →