Convert binary to decimal

by Amol 21. February 2011 19:18

CREATE FUNCTION [dbo].[GetDecimal] (@BinaryNumber BIGINT)

RETURNS BIGINT

AS

BEGIN

   

    DECLARE  @DecimalNumber BIGINT

            ,@Divider BIGINT

            ,@BinaryNumberLength INT

           

    SET @BinaryNumberLength = LEN(@BinaryNumber) - 1

    SET @Divider = '1' + REPLICATE('0', @BinaryNumberLength);

      

    WITH cte (Id, Divider, lvl) 

    AS

    (

    SELECT 

         @BinaryNumber / @Divider AS Id

        ,@Divider/10 AS Divider

        ,@BinaryNumberLength AS lvl

    UNION ALL

    SELECT

         @BinaryNumber / Divider AS Id

        ,Divider/10 AS Divider

        ,lvl - 1

    FROM cte

    WHERE Divider > 0

    )

    SELECT @DecimalNumber = SUM(RIGHT(Id, 1) * POWER(2, Lvl))

    FROM cte

       

    RETURN (@DecimalNumber)

 

END


Tags:

SQL Server Scripts

T-SQL to Convert rows into single column

by Amol 17. February 2011 19:11

Many times we need to convert column values into rows. There are two scenarios here. First one is to concatenate all the values in column. And second one is to concatenate all the values in column along with another grouping column.

Consider you have below table

   CREATE TABLE [dbo].[MyTable]

   (

     Id INT,

     Name VARCHAR(255)

   )

   GO

Insert some data in above table.

   INSERT INTO [dbo].[MyTable] 

      (Id, Name)

   SELECT 1, 'A' UNION ALL   

   SELECT 1, 'B' UNION ALL

   SELECT 1, 'C' UNION ALL

   SELECT 1, 'D' UNION ALL

   SELECT 2, 'E' UNION ALL

   SELECT 2, 'F' UNION ALL

   SELECT 2, 'G' UNION ALL

   SELECT 3, 'H' UNION ALL

   SELECT 3, 'I' UNION ALL

   SELECT 4, 'J'

 

   GO

So you have following data in [dbo].[MyTable]

   Id  Name 
   ---------------------------
   1   A
   1   B
   1   C
   1   D
   2   E
   2   F
   2   G
   3   H
   3   I
   4   J

First – Concatenate all the values in column “Name”

   DECLARE @StrSQL VARCHAR(8000)

   SELECT @StrSQL = COALESCE(@StrSQL + ',', '') + Name

   FROM [dbo].[MyTable]

 

   SELECT @StrSQL

 

   GO

   Name 
   ------------------------
   A,B,C,D,E,F,G,H,I,J

 

Second – Concatenate all the values in column “Name” grouping by column “Id”

   SELECT  

      Id,

      STUFF( (

                  SELECT

                  ',' + Name AS 'text()'

                  FROM [dbo].[MyTable] t2

                  WHERE t2.id = t1.id

                  FOR XML PATH('')

               )

               ,1

               ,1

               ,''

             ) AS Name

   FROM [dbo].[MyTable] t1

   GROUP BY Id

   GO

   Id    Name
   -------------------------
   1     A,B,C,D
   2     E,F,G
   3     H,I
   4     J

 

Tags:

SQL Server Scripts

T-SQL to create random number

by Amol 17. February 2011 19:03

We often need of creating random numbers which are useful to create sample or test data.

Here are two ways to create the random number in SQL Server

1.       Create random number less the given number

Below query will create a random number between 0 and 1000

 

SELECT ABS(CAST(NEWID() AS binary(6)) % 1000) AS Value

 

2.       Create random number between minimum and maximum numbers

Below query will create the random number between minimum and maximum numbers

DECLARE @MinValue integer = 10,

              @MaxValue integer = 100

SELECT CAST((@MaxValue - @MinValue + 1) * RAND() AS integer) + @MinValue

 

3.       Each time randomly select top N records from a table

Below query will select top 10 records from table Numbers. In this scenario I have populated Numbers table with 1 to 10000 numbers. Each time you fire a query, we will be able to get different top N numbers from Numbers table.

SELECT TOP 10 *

FROM [dbo].[Numbers]

ORDER BY NEWID()

 

Tags:

SQL Server Scripts

Category

Recent Posts

Tag cloud