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
SQLZealots 