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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s