If your xml data contains a namespace, you also need to query the data using the same xml namespace. DECLARE@Sample TABLE ( rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, [xml] XML ) INSERT@Sample SELECT'<stringList xmlns="http://schemas.
Read more →
Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item. And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking.
Read more →
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 →
This blog post is continued here http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx /******************************************************************************* Prepare script with testcase *******************************************************************************/ DECLARE@XMLString XML, @ElementEqual VARCHAR(50), @ElementLike VARCHAR(50), @AttributeEqual VARCHAR(50), @AttributeLike VARCHAR(50) SELECT@XMLString = ' <Customers> <Customer> <FirstName>Kevin</FirstName> <LastName>Goff</LastName> <City type="aca">Camp Hill</City> </Customer> <Customer> <FirstName>Steve</FirstName> <LastName>Goff</LastName> <City type="acb"> Philadelphia</City> </Customer> </Customers>', @ElementEqual = 'Camp Hill', @ElementLike = 'adel', @AttributeEqual = 'acb', @AttributeLike = 'c' /******************************************************************************* Search for City-nodes *******************************************************************************/ -- Get all City where element is equal to "Camp Hill" SELECTcust.
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 →
DECLARE@Interfaces TABLE ( RowID INT IDENTITY(0, 1), Interface CHAR(38), IP VARCHAR(15) ) INSERT@Interfaces ( Interface ) EXECmaster..xp_regenumkeys N'HKEY_LOCAL_MACHINE', N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces' DECLARE@RowID INT, @IP VARCHAR(15), @Key NVARCHAR(200) SELECT@RowID = MAX(RowID) FROM@Interfaces WHILE@RowID >= 0 BEGIN SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface FROM @Interfaces WHERE RowID = @RowID EXEC master.
Read more →
I started out with typing SELECT@@VERSION and got the result as Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86) Dec 8 2007 18:51:32 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.
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 →