Sreenivasa Reddy Thotli

SELECT RandomNumber = CONVERT(VARCHAR,ABS(CHECKSUM(NEWID()))) FROM master.dbo.spt_values ORDER BY RandomNumber

CREATE function [dbo].[fn_WeekStartDate] ( @date datetime ) returns datetime as begin declare @dtweek int declare @weekstart DateTime Declare @NoOfDays int Declare @t varchar(100) set @dtweek = datepart(weekday, @date) -1 Set @weekstart = DateAdd(Day,-@dtweek,@date) if datepart(month,@weekstart) datepart(month,@date) BEGIN Set @t = Convert(VARCHAR,DatePart(month,DateAdd(month,0,@date))) + '/01/'+ Convert(VARCHAR,datepart(year,@date)) Set @NoOfDays = DAteDiff(day,@t,@date) Set @weekstart = DateAdd(Day,-@NoOfDays,@date) SELECT @weekstart = @t END return @weekstart end Ex: select dbo.[fn_WeekStartDate](getdate())

CREATE function [dbo].[fn_WeekEndDate] ( @date datetime ) returns datetime as begin declare @dtweek int declare @weekend DateTime Declare @NoOfDays int Declare @t varchar(100) set @dtweek = 7- datepart(weekday, @date) Set @weekend = DateAdd(Day,@dtweek,@date) if datepart(month,@weekend) datepart(month,@date) BEGIN Set @t = Convert(VARCHAR,DatePart(month,DateAdd(month,1,@date))) + '/01/'+ Convert(VARCHAR,datepart(year,@date)) Set @NoOfDays = DAteDiff(day,@t,@date) +1 Set @weekend = DateAdd(Day,-@NoOfDays,@date) END return @weekend end Ex : select dbo.[fn_WeekEndDate](getdate())

CREATE function [dbo].[SplitByString] ( @string nvarchar(4000), @delimiter nvarchar(10), @addDelimetrContent bit ) returns @table table ( [Value] nvarchar(4000) ) begin declare @nextString nvarchar(4000) declare @pos int, @nextPos int SET @nextString = '' SET @string = @string + @delimiter SET @pos = CHARINDEX(@delimiter, @string) SET @nextPos = 1 WHILE (@pos 0) BEGIN IF (@addDelimetrContent = 1) SET @nextString = @delimiter + SUBSTRING(@string, 1, @pos - 1) ELSE SET @nextString = SUBSTRING(@string, 1, @pos - 1) IF (@nextString != '' AND @nextString != @delimiter) BEGIN INSERT INTO @table ([Value]) VALUES (@nextString) END SET @string = SUBSTRING(@string, @pos + LEN(@delimiter), LEN(@string)) SET @nextPos = @pos SET @pos = CHARINDEX(@delimiter, @string) END RETURN END Ex : select * from dbo.[SplitByString]('http://www.google.comhttp://gmail.comhttp://yahoo.com','h...

CREATE FUNCTION [dbo].[udf_GetNumeric] (@strAlphaNumeric VARCHAR(256)) RETURNS VARCHAR(256) AS BEGIN DECLARE @intAlpha INT SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric) BEGIN WHILE @intAlpha > 0 BEGIN SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' ) SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric ) END END RETURN ISNULL(@strAlphaNumeric,0) END Ex : select dbo.[udf_GetNumeric]('xyz @abc123')

CREATE FUNCTION [dbo].[StripNonAlphaNumerics] ( @s VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @p INT = 1, @n VARCHAR(8000) = ''; WHILE @p Ex : select dbo.StripNonAlphaNumerics('0xyz^ dd')

CREATE FUNCTION [dbo].[WordCount] ( @InputString VARCHAR(max) ) RETURNS INT AS BEGIN DECLARE @Index INT DECLARE @Char CHAR(1) DECLARE @PrevChar CHAR(1) DECLARE @WordCount INT SET @Index = 1 SET @WordCount = 0 WHILE @Index <=LEN(@InputString) BEGIN SET @Char = SUBSTRING(@InputString, @Index, 1) SET @PrevChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING(@InputString, @Index - 1, 1) END IF @PrevChar = ' ' AND @Char != ' ' SET @WordCount = @WordCount + 1 SET @Index = @Index + 1 END RETURN @WordCount END