SELECT* FROM::fn_dblog(DEFAULT, DEFAULT) AS l INNERJOIN sysobjects AS so ON so.name = l.[transaction name] SELECTso.name AS ObjectName, so.type AS ObjectType, MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime FROM::fn_dblog(DEFAULT, DEFAULT) l innerjoin sysobjects so on so.
Read more →
DECLARE@Value NVARCHAR(200) SET@Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff' WHILE@Value LIKE '%[^0-9]%' SET @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '') SELECT@Value
Legacy Comments
Uri Dimant
2008-11-12
re: Stripping out all non-numeric characters from a string declare @string varchar(200)
Read more →
SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly
Legacy Comments
Bill Curnow
2008-11-12
re: Getting date or time only from a Datetime value SELECT GETDATE() AS fullDateTime,
Read more →
-- Prepare sample data DECLARE@Master TABLE ( ID INT, Name VARCHAR(20) ) INSERT@Master SELECT1, 'Peso' UNION ALL SELECT2, 'SQLTeam' UNION ALL SELECT3, 'SQL' DECLARE @Child TABLE ( MasterID INT, ID INT, Value VARCHAR(20) ) INSERT@Child SELECT1, 1, 'Row 1 for Peso' UNION ALL SELECT1, 2, 'Row 2 for Peso' UNION ALL SELECT2, 3, 'Row 1 for SQLTeam' -- Display the XML SELECTePurchaseOrder.
Read more →
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 →
/******************************************************************************* Initialize communication variables *******************************************************************************/ SETNOCOUNT ON DECLARE@pathProc VARCHAR(255), @pathFunc VARCHAR(255), @pathTrig VARCHAR(255), @pathView VARCHAR(255), @cmd NVARCHAR(4000), @pathBase VARCHAR(256) SELECT@pathBase = '\\Archive\Documents\Projects\Peso\Code\', @pathProc = @pathBase + 'Stored Procedures\', @pathFunc = @pathBase + 'Functions\', @pathTrig = @pathBase + 'Triggers\', @pathView = @pathBase + 'Views\' SET@cmd = 'md "' + @pathProc + '"' EXECmaster.
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 →
This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008. For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to.
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 →