sinairv

Regular Expression Recipes

Identical character repetition detection

Match 2 repetitions of a character

(.)\1

Match 3 repetitions of a character

(.)\1{2}

sinairv

-- how to convert a decimal number to a 'hh:mm' formatted hour. E.g., 
-- 2.25 -> 02:15
-- 13.5 -> 13:30
-- 2.10 -> 02:06
DECLARE @Value DECIMAL(18,2) = 2.10;

SELECT RIGHT('00' + 
            CONVERT(VARCHAR, CONVERT(INT, @Value)), 2) 
        + ':' + 
        RIGHT('00' + 

sinairv

;
; "CMD Prompt Here" PowerToy
;
; Copyright 1996 Microsoft Corporation
;
; Modified to launch VS.NET 2010 command prompt 5/6/03 MG
; Modified to launch VS.NET 2012 command prompt 9/4/12 KZU
; Modified to add context menu to a directory background 9/27/12 SinaIRV

[version]

sinairv

-- Template for creating and testing SQL function and view pairs

IF EXISTS 
    (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'ufnSomeFunction') 
        AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnSomeFunction]
GO

CREATE FUNCTION [dbo].[ufnSomeFunction]

sinairv

-- Template for creating and testing SQL views 

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vSomeView')
DROP VIEW [dbo].[vSomeView]

GO

CREATE VIEW [dbo].[vSomeView] AS
SELECT * FROM SomeTable

sinairv

-- Template for creating and testing SQL table-valued functions 

IF EXISTS 
    (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'ufnSomeFunction') 
        AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnSomeFunction]
GO

CREATE FUNCTION [dbo].[ufnSomeFunction]

sinairv

-- We want to return one phone number per each person-id
-- If a phone number is flagged as default it has a higher priority
-- There may be more than one phone numbers be marked as default, 
-- or one person may have several phone numbers none of which is marked as default


-- Table Definition
CREATE TABLE [dbo].[PersonPhone](
	[PersonId] [int] NOT NULL,
	[Phone] [varchar](50) NOT NULL,

sinairv

CREATE procedure [dbo].[uspFindAllGuid] (@guidToFind uniqueidentifier)
	as

SET NOCOUNT ON

-- table variable to store all table and columns containing GUIDs
DECLARE @GuidCols AS Table
(
    RowIndex int,
    TableName varchar(50),

sinairv

SELECT C.TABLE_NAME, C.COLUMN_NAME FROM 
INFORMATION_SCHEMA.COLUMNS C 
INNER JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'BASE TABLE'
WHERE C.DATA_TYPE = 'uniqueidentifier'

sinairv

-- Populate test data from values specified for a subset of columns

USE TestDataBase;
GO

-- think of it as the main table to be filled
CREATE Table #SomeTable
(
    ID uniqueidentifier,
    Name varchar(50),