Working with SHA1 on SQL Server

There are times you might need to convert some information to MD5 on your SQL Server in HEX format. As it is for now there is no out of the box function to do it on SQL Server. But you can easily accomplish this task by using the following function:

create FUNCTION [dbo].[sha1]
(
        @value varchar(4000)
)
RETURNS varchar(40)
AS
BEGIN
  return
    case when @value is not null then
      SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', @value)), 3, 40)
    else null end
END
GO

Testing it is quite straight forward. For example:

select dbo.sha1('123') 

And here is the output:

40bd001563085fc35165329ea1ff5c5ecbdbbeef

 

Working with MD5 on SQL Server

There are times you might need to convert some information to MD5 on your SQL Server in HEX format. As it is for now there is no out of the box function to do it on SQL Server. But you can easily accomplish this task by using the following function:

create FUNCTION [dbo].[md5]
(
        @value varchar(4000)
)
RETURNS varchar(40)
AS
BEGIN
  return
    case when @value is not null then
      (SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', @value)), 3, 32))
    else null end
END
GO

Testing it is quite straight forward. For example:

select dbo.md5('123') 

And here is the output:

202cb962ac59075b964b07152d234b70 

 

Generating random hex strings using MS SQL Server

In the post Generating random numbers in a specific range using MS SQL Server we saw how it is possible to generate random integer number for a given range. We will use the function dbo.random_range from this post in order to create a function that generates a random string of hex numbers of any length.
The idea is very simple. Suppose we would like to generate a string of length N, then all we have to do is loop N times and generate a random number POSITION in the range of 1 to 16. For each of the random values we take a substring of one character in the generated POSITION from a predefined string containing all Hex digits (“0” to “9” and “a” to “f”). After we done we will have a string containing N Hex digits.
Let’s have a look at the SQL SERVER code:

CREATE FUNCTION random_hex(@length int)
RETURNS varchar(2000)
AS
BEGIN
  DECLARE @index int
  DECLARE @seq varchar(16) 
  DECLARE @result varchar(2000)
  DECLARE @digit char(1)
  SET @seq = '0123456789abcdef'
  SET @index = 0
  SET @result = ''
  WHILE @index < @length
  BEGIN
    SET @digit = SUBSTRING(@seq, dbo.random_range(1, 16), 1)
    SET @result = @result + @digit
    SET @index = @index + 1
  END 
  RETURN @result
END
GO

Generating random numbers in a specific range using MS SQL Server

In the post Generating Random numbers in a SELECT statement using MS SQL Server we saw how random numbers can be generated using Microsoft MS SQL Server. We will use the dbo.random function from this blog in order to create a new function called dbo.random_range.
This function will generate a random number for a specific range. For example: we might need to generate a random number between 100 and 1000.
The dbo.random_range function will return a random number between @start and @end parameters. The dbo.random function generates a random number between 0 and 1. In order to create a random number for a given range we will use a small mathematical calculation which is pretty easy and straightforward. Therefore we will simply understand it by looking at the dbo.random_range function:

CREATE FUNCTION [dbo].[random_range](@start int, @end int)
RETURNS int
AS
BEGIN
  return @start + dbo.random() * (@end - @start + 1)
END

Generating Random numbers in a SELECT statement using MS SQL Server

Generating random numbers in SQL statement is not as trivial as it may look. Simply invoking the rand() method in a query will not work. For example, if you have a table with 1000 rows, doing:

select rand() from my_table

Will not work.

The result of this query will be 1000 rows of the same random number. MS SQL optimizer evaluates the rand() function only once.

In order to override this problem, we can do the following steps:

1) Create a view named: random_view that selects rand() function:

create VIEW [dbo].[random_view]
AS
SELECT rand() 'rnd'   

2) Create a function that makes a select on the random_view:

CREATE FUNCTION [dbo].[random]()
RETURNS float
AS
BEGIN
return (select rnd from random_view)
END

Now we can use our new generated function in a any query:

select dbo.random() from my_table

Assuming my_table has has 1000 rows, we will get 1000 different random numbers.

Convert IP String to numeric representation and numeric representation to IP String in SQL Server

In the post Convert IP String to numeric representation and numeric representation to IP String in Java we dealt with IP conversions in Java. In this post we will do just the same, but instead using Java we will use SQL Server. Converting the Java code to MS SQL is very simple. Not much knowledge in T-SQL is needed, because we are mostly dealing with mathematical operations. We will simply create 2 functions. Here is the first function for converting bigint to IP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[bigint_to_ip]
(
        @ip bigint
)
RETURNS varchar(15)
AS
BEGIN
    DECLARE @octet0 varchar(3)
    DECLARE @octet1 varchar(3)
    DECLARE @oct
et2 varchar(3)
    DECLARE @octet3 varchar(3)
    SET @octet3 = (@ip / power(2, 24)) % 256
    SET @octet2 = (@ip / power(2, 16)) % 256
    SET @octet1 = (@ip / power(2, 8)) % 256
    SET @octet0 = @ip % 256
    RETURN @octet3 + '.' + @octet2 + '.' + @octet1 + '.' + @octet0
END

The second function which converts IP to bigint is a bit more tricky, since unlike java “split” function, SQL Server T-SQL doesn’t present any comfortable way of handling strings in the manner Java does. Luckily, SQL Server does have a function called “PARSENAME” which is used for getting a part of an object. Assuming the parts of an object are separated with dots, it is possible to get any part of the object. for example:

DECLARE @myObject varchar(100)
SET @myObject = 'this.is.my.object'
PARSENAME(@ObjectName, 4) -- Return: this
PARSENAME(@ObjectName, 3) -- Return: is
PARSENAME(@ObjectName, 2) -- Return: my
PARSENAME(@ObjectName, 1) -- Return: object

Note, that the position is reversed to the intuitive logic, and that position “1” means the last part of the string and not the first.

We can use the “PARSENAME” function in order to easily split the IP string to its components. Here is the second function that converts IP to bigint:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ip_to_bigint]
(
        @ip varchar(15)
)
RETURNS bigint
AS
BEGIN
    DECLARE @octet0 bigint
    DECLARE @octet1 bigint
    DECLARE @octet2 bigint
    DECLARE @octet3 bigint
    SET @octet3 = ParseName(@ip, 4)
    SET @octet2 = ParseName(@ip, 3)
    SET @octet1 = ParseName(@ip, 2)
    SET @octet0 = ParseName(@ip, 1)
    RETURN @octet3 * power(2, 24) + @octet2 * power(2, 16) + @octet1 * power(2, 8) + @octet0
END