Wednesday, 3 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','http',1)

Note : Make Sure that if @addDelimetrContent = 1 then we will add delimeter string to each row otherwise will add as empty.

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