Split a given string into words based on the delimiter.

We can split a string into different words based on the delimiter. This example shows how we can achieve this. We can also use recursive CTE but if the string is too long MAXRECURSION can be encountered.

--DECLARE THE VARIABLES

DECLARE @String NVARCHAR(4000)
DECLARE @Delimiter NVARCHAR(10)
DECLARE @DelimiterPosition INT
DECLARE @StartofString INT
DECLARE @EndofString INT
DECLARE @DelimiterLength INT

--INITIALIZE THE VARIABLES

SET @String = 'Welcome,to,the.'
SET @Delimiter = ','
SET @DelimiterLength = LEN('.'+@Delimiter+'.')-2 --WILL BE USEFUL WHEN WE HAVE WHITE SPACE AS DELIMITER
SET @StartofString = 1
SET @EndofString = LEN(@String)+1
SET @DelimiterPosition = 0

WHILE (@DelimiterPosition < @EndofString)
BEGIN

SET @DelimiterPosition = CHARINDEX(@Delimiter,@String,@StartofString)
IF(@DelimiterPosition = 0)
SET @DelimiterPosition = @EndofString --FOR THE LAST SPLIT WORD, THE DELIMITER POSITION WILL BE ONE. SO, WE SET IT TO END OF THE STRING
PRINT SUBSTRING(@String,@StartofString,@DelimiterPosition-@StartofString)
SET @StartofString = @DelimiterPosition+@DelimiterLength
END


If we want to use CTE the following code will do exactly as above


DECLARE @Delimiter NVARCHAR(10)
DECLARE @String NVARCHAR(4000)
SET @String = 'Welcome,to,the,new'
SET @Delimiter = ','

;WITH Split
AS(
SELECT SUBSTRING(@String+@Delimiter,1,CHARINDEX(@Delimiter,@String+@Delimiter)-1) AS Word
,SUBSTRING (@String+@Delimiter,CHARINDEX(@Delimiter,@String+@Delimiter)+1,LEN(@String+@Delimiter)) As Remaining
   
    UNION ALL
   
SELECT SUBSTRING(Remaining,1,CHARINDEX (@Delimiter,Remaining)-1) AS Word
,SUBSTRING (Remaining,CHARINDEX(@Delimiter,Remaining)+1,LEN(Remaining)) As Remaining
     
FROM split
  WHERE CHARINDEX (',',Remaining)<>0
  )
 
  SELECT Word FROM Split

Know whether a number is power of two

There are several ways to find whether a given number is power of two. I am sharing two ways by which we can see if the number is power of two or not. There is a basic rule to know whether a given number is power of two or not. (N) & (N-1) is Zero. Here & is Bitwise and. When we use &, SQL server internally converts the given Number into Binary and performs Bitwise and. Take an example.
N=2 Binary is 10
N-1 = 1 Binary is 01
N& N-1 =
10
01
---
00
---
This is simple logic. This is the SQL code


DECLARE @N INT
SET @N =  -- pass a value
IF(@N & (@N-1))=0
PRINT 'Pow of two'
ELSE
PRINT 'Not pow of two'

The other way is to do it programatically. Here is how you can do



DECLARE @IncomingNumber int
SET @IncomingNumber = --pass a value
DECLARE @BinNumber VARCHAR(200)
SET @BinNumber = ''

WHILE @IncomingNumber <> 0
BEGIN

IF(@IncomingNumber%2 = 0)
SET @BinNumber = CAST(0 AS VARCHAR(200))+ @BinNumber
ELSE IF(@IncomingNumber%2=1)
SET @BinNumber= CAST(1 AS VARCHAR(200))+@BinNumber

SET @IncomingNumber = @IncomingNumber / 2

END

SELECT @BinNumber
IF(SUBSTRING(@BinNumber,1,1)=1 AND CAST(SUBSTRING(@BInNumber,2,LEN(@BinNumber)) AS INT) =0)
PRINT 'Pow of two'
ELSE
PRINT 'Not Pow of two'

Convert an Integer to Binary

Here is a quick and fast way to convert an integer to Binary number. Binary number is base to two. That is we have to divide the number by two and take the remainder in the reverse order. In SQL server we can achieve this by this simple script.



DECLARE @InputValue int SET @InputValue = --Specify the number
DECLARE @Binary VARCHAR(200)
SET @Binary = '' -- Initialize to nothing
WHILE @InputValue <> 0
BEGIN
SET @Binary = SUBSTRING('01', (@InputValue % 2) + 1, 1) + @Binary
SET @InputValue = @InputValue / 2
END SELECT @Binary

Look at SUBSTRING() . I hardcoded '01' and taking the length of  Input value. Remember when you divide any number by two, the remainder will be either 0 or 1 and it won't go beyond. Once Remainder is taken, take the substring of '01' the values would be SUBSTRING('01', 1 OR 2,1). So we get either 0 or 1 which is binary. We add the previous binary value to the existing substring. Remember @Binary is string so, the order is important. append @Binary at the end. When you divide a number by 2 and try to take its value, always remember that the remainders are taken in bottom up approach. That is why I am appending the previous remainder.