Posts

Showing posts from September, 2014

How to generate random numbers in sql server

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

How to get week start date for particular date in a month in sql server

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())

How to get week end date for particular date in a month in sql server

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())

How to split a string with specific words in sql server

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'

How to get Only Numerics from a string in sql server

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')

How to remove special characters from a string (non alpha numeric characters) in sql server

CREATE FUNCTION [dbo].[StripNonAlphaNumerics] ( @s VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @p INT = 1, @n VARCHAR(8000) = ''; WHILE @p <= LEN(@s) BEGIN IF SUBSTRING(@s, @p, 1) LIKE '[A-Za-z0-9]' BEGIN SET @n += SUBSTRING(@s, @p, 1); END SET @p += 1; END RETURN(@n); END Ex : select dbo.StripNonAlphaNumerics('0xyz^ dd')