Category: FUN with SQL

FUN with SQL, SQL

Palindrome in SQL Server

What is Palindrome?

From Books OnLine, “A palindrome is a word, number, phrase, or other sequence of characters which reads the same backward as forward, such as madam, racecar. There are also numeric palindromes, including date/time stamps using short digits 11/11/11 11:11 and long digits 02/02/2020. Sentence-length palindromes ignore capitalization, punctuation, and word boundaries.”

Code implementation

The below function eliminates characters other than alphanumeric characters. The function is modified to accommodate alphabets to the old function written to extract only numbers. 

create or alter function dbo.fn_extractalphanumeric (@ValueString nvarchar(max))
returns nvarchar(max)
as
Begin

Declare @retvaluestring nvarchar(max)

;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4)
Select @retvaluestring =(
SELECT  (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9a-z]'
                       THEN SUBSTRING(string, n, 1)
                      ELSE ''
                  END + ''
           FROM Nums
           WHERE n <= LEN(string)
           FOR XML PATH( '' )) AS stringout
FROM (Select * from (values(@ValueString)) A(string))A)
return @retValuestring
End

Test Script

DECLARE @Sample TABLE(string VARCHAR(2000));
INSERT INTO @Sample
Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('aA1b2c3d4ee55fff666gggg7777h'),('Al1')
,('2Bob$'),('!Carl%'),('%D#?:"{}|\][+_)(*&^	%$#@!~`_+?>a*vi()(d&*(&*#?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' ),
('    Eddie     '),('Noon'),('My Gym'),('malayalam'),('11:11'),('11/11/1111'),('racecar')

Select string, dbo.fn_extractalphanumeric(string),
	case when reverse(dbo.fn_extractalphanumeric(string)) = dbo.fn_extractalphanumeric(string) 
			then 'Palindrome' else 'Not Palindrome' End stringtype
From @Sample

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

FUN with SQL, SQL

Fibonacci series in SQL Server
Today, I noticed that my elder kid was learning about fibonacci series. While observing her, I was just trying to write SQL script to produce the series just for fun and sharing it with you all.

What is fibonacci series?

A series of numbers in which each number ( Fibonacci number ) is the sum of the two preceding numbers. The simplest is the series 1, 1, 2, 3, 5, 8, etc. Fibonacci was not the first to know about the sequence, it was known in India hundreds of years before! Leonardo Pisano Bogollo is known as fibonacci man, and he lived between 1170 and 1250 in Italy. “Fibonacci” was his nickname, which roughly means “Son of Bonacci”.

Fibonacci Day is November 23rd, as it has the digits “1, 1, 2, 3” (11 represents month number-23 represents day part) which is part of the sequence. So next Nov 23 let everyone know about this fact.(Honestly, I never noticed this until now.)

Formula & Code Implementation

The simple formula is Fn = Fn-1 + Fn-2

The code implementation as below :

I used Common Table Expression (CTE) to generate it easily over while loop. If you guys are comfortable with while loop, you can even generate with loop.

Script: 
Declare @UntilNo integer = 100
;With Fibonacci (N, NextN) AS
(
 SELECT 0, 1
    UNION ALL
    SELECT NextN, NextN+ N
    FROM Fibonacci
    WHERE N < @UntilNo
)
SELECT Substring(
    (SELECT cast(', ' as varchar(max)) + cast(N as varchar(max)
)
FROM Fibonacci
FOR XML PATH('')),3,10000000) AS list

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

FUN with SQL, SQL

How to identify Prime numbers in SQL Server
While on HackerRank puzzles, I came through a question to find out prime numbers in SQL Server. Here is my try as below. You may try your own and share the code in the comment. 
declare @max INT = 1000,@s varchar(max) =''

;WITH Tally_Numbers(n) AS
(
    SELECT 2
    UNION ALL
    SELECT n+1 FROM Tally_Numbers WHERE n <= @max
)
,cte1 as(
select Tally1.n 
from Tally_Numbers Tally1
where not exists (select 1 from Tally_Numbers Tally2 where Tally2.n < Tally1.n AND Tally1.n % Tally2.n = 0)
)
SELECT STUFF((
SELECT '&' + CAST(r.N AS VARCHAR(10)) AS "text()"
FROM cte1 r
ORDER BY r.N
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(8000)'),1,1,'')
OPTION (MAXRECURSION 0);

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

FUN with SQL, SCRIPT GALLERY, SQL

Fun with SQL – Find out numbers where adjacent digit differs by 1
Today, I have come through a post by one of my good friends and one of the most famous SQL Server experts in India – Madhivanan.

The question was : “Given a number N, write a code to print all positive numbers less than N in which all adjacent digits differ by 1” Here is my attempt to solve this using SQL Server T-SQL 
--Provide input value 
declare @number int
set @number=105
 
--Create a temporary table to store numbers
Drop table if exists #temp
create table #temp (number int)

;with ctebuilder as(
select
    row_number () over (order by (select null)) as sno 
from
    sys.sysobjects as t1 cross join sys.sysobjects as t2 ) 
Insert into #temp(number) Select sno From ctebuilder where sno<=@number

;with cte as(
select
    t1.number number, 
    cast(substring(cast(t1.number as varchar(20)),t2.number,1) as int) as number2, 
	row_number () over(partition by t1.number order by (select null) asc) Rn
from #temp as t1 cross join #temp as t2
where t2.number <= len(t1.number) and t1.number <=@number
)
Select number From(
 Select * ,LAG(number2,1) OVER (partition by number
		ORDER BY (select NULL) asc
	) previous_val From cte
	) A group by number 
having count(case when previous_Val - number2 in (1,-1) then 1 Else NULL End )  
= count(number2)-1

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

FUN with SQL, SQL, T-SQL

Travel Planner in SQL Server

Everyone needs a travel plan especially if you are working in India, it is tough to reserve your tickets as it needs. I had lot of bad experiences missing my reservations without a well in advance booking – that pays me a lot.

Here, I would like to share with you one of my SQL Server Script prepared for my travel plan.

The idea is very simple – I would love to see my leave dates and the number of days that I would get. I also wanted to look at an option with a voluntary leave to get more holidays. 🙂

The script is very much configurable and easy to understand, so that anyone can change as per the requirement.

The below screenshot is a sample output of the Script:

Train_Calendar

Script:

Declare @LeaveCalendar as Table (Calendar_Date Date,Train_Date Date,WeekName Varchar(10), Is_NationalHoliday Bit,Is_LocalHoliday Bit, Holiday_Description Varchar(100))

Declare @CalendarYear char(4) = '2015'
Declare @BookingPeriod int = 60 --No.Of days prior the ticket needs to be booked.

;With cteCalendar
as
(
 Select DATEADD(yy, DATEDIFF(yy,0,@CalendarYear + '-01-01'), 0) CalendarDate
 Union All
 Select DATEADD(day,1,Calendardate) CalendarDate From cteCalendar
 Where CalendarDate < DATEADD(yy, DATEDIFF(yy,0,@CalendarYear + '-01-01') + 1, -1)
)
Insert into @LeaveCalendar(Calendar_Date,Train_Date,WeekName)
Select CalendarDate,DATEADD(day,-(@BookingPeriod),CalendarDate),DATENAME(WEEKDAY,CalendarDate) From cteCalendar OPTION(MAXRECURSION 366)

--Saturday and Sunday List Holiday update
Update @LeaveCalendar Set Is_NationalHoliday = 1, Holiday_Description = WeekName
Where WeekName in ('Sunday','Saturday')

--National Holiday List
;with cte(sDate,sDescription) as 
(
 Select @CalendarYear+'-01-01' ,'New Year' 
 Union All
 Select @CalendarYear+'-01-26' ,'Republic Day'
 Union All
 Select @CalendarYear+'-05-01' ,'May Day'
 Union All
 Select @CalendarYear+'-10-02' ,'Gandhi Jayanthi'
 Union All
 Select @CalendarYear+'-12-25' ,'Christmas Day'
)
Update A Set Is_NationalHoliday = 1,Holiday_Description = B.sDescription 
From @LeaveCalendar A
Inner join cte B On A.Calendar_Date = B.sDate 

--LocalHoliday List(The below is a sample, you may add your local holidays in the list)
;with cte(sDate,sDescription) as 
(
 Select @CalendarYear+'-01-15' ,'Pongal' 
 Union All
 Select @CalendarYear+'-04-14' ,'Tamil New Year' 
 Union All
 Select @CalendarYear+'-08-20' ,'Onam' 
 Union All
 Select @CalendarYear+'-09-17' ,'Ganesh Chaturthi'
 Union All
 Select @CalendarYear+'-10-21' ,'Ayudha Puja'
 Union All
 Select @CalendarYear+'-11-10' ,'Diwali'

)
Update A Set Is_LocalHoliday = 1,Holiday_Description = B.sDescription 
From @LeaveCalendar A
Inner join cte B On A.Calendar_Date = B.sDate 

;With cte as
(
Select *,Coalesce(Is_NationalHoliday,Is_LocalHoliday) IsHoliday From @LeaveCalendar
)
,cteHolidayPeriod as
(
 Select *,Coalesce(IsHoliday,Case when Lead(IsHoliday,1)Over(Order by Calendar_date) = Lag(IsHoliday,1)Over(Order by Calendar_Date) 
 and Lag(IsHoliday,1)Over(Order by Calendar_Date) =1 Then 1 
 Else 0 End) as 'Holiday_Period_Voluntry',
 Case when Lead(IsHoliday,1)Over(Order by Calendar_date) = Lag(IsHoliday,1)Over(Order by Calendar_Date) 
 and Lag(IsHoliday,1)Over(Order by Calendar_Date) =1 and IsNull(IsHoliday,0) = 0 Then 1 
 Else NULL End Is_VoluntryHoliday
 From cte
)
,cteReadable as
(
 Select 
 *,datediff(day, '19000101', Calendar_Date) - ROW_NUMBER() OVER (order by Calendar_date) as GroupID 
 From cteHolidayPeriod Where Holiday_Period_Voluntry=1
)
Select *,
(Select Top 1 Holiday_Description From @LeaveCalendar Where Calendar_Date between Calendar_HolidayStartDate and Calendar_HolidayEndDate
 and 1 in (Is_LocalHoliday,Is_NationalHoliday) and Weekname not in ('Saturday','Sunday')) 'Holiday Description'
From(
Select 
 Min(Calendar_date) Calendar_HolidayStartDate,Max(Calendar_date) Calendar_HolidayEndDate,
 DateAdd(day,-1,Min(Train_Date)) Booking_HolidayStartDate,Max(Train_Date) Booking_HolidayEndDate,
 Count(Calendar_Date) 'No.Of Holidays', Count(Is_VoluntryHoliday) 'No.Of Voluntry Holiday'
From cteReadable
Group by GroupID having Count(Calendar_Date)>2)A