SQL SERVER – Check If String is a Palindrome in Using T-SQL Script – Reverse Function

March 7, 2013

One of my friends who works in a big MNC recently asked me that if there is any way to check if the String is Palindrome or not. It is a word, phrase, or sequence that reads the same backward as forward. For example A man, a plan, a canal – Panama! is palindrome so as Was it a car or a cat I saw?

My first reaction was to him was why does this kind of functionality. His answer was they have requirement in their business application where they are building captcha and they may display the image in mirror image as well as a part of challenge code and he can’t have any word which is palindrome as an option. For this he wanted to write a script which will go letter by letter and match them. If they are same, he will not use the word for captcha.

Honestly there is no need to write a function which compares it word by word. You can just use a REVERSE function to do this as well.

DECLARE @PalinString VARCHAR(256) = 'Was it a car or a cat I saw'
SELECT CASE WHEN REPLACE(@PalinString, ' ', '') = 
REVERSE(REPLACE(@PalinString, ' ', ''))
THEN 'Palindrome'
ELSE 'Not Palindrome' END AS [Answer]

Again, if the word is not Palindrome you can just will get answered as it is not a palindrome. My script currently is removing all the spaces from the string. However, if your string is like A man, a plan, a canal – Panama! you may have to remove the exclamation mark and comma too using the REPLACE function.

Additionally, this works with foreign language as well. See the image below.

SQL SERVER - Check If String is a Palindrome in Using T-SQL Script - Reverse Function foreignlanguage

Let me know what you think of this function, do you think there should be a better trick to identify Palindrome? If yes, please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts, SQL Server, SQL String

SQL SERVER – Watch Four Efficiency Tricks in SQL Server In Sixty Seconds – Subscribe for SQL Learning Videos

SQL SERVER – Tricky Question – What is the Default Size of the Database

How to Map Network Drive as Fixed Drive? – Interview Question of the Week #258

January 5, 2020

SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

April 23, 2007

SQL SERVER – Reading Transaction Log to Identified Who Dropped a Table

May 17, 2018

13 Comments. Leave new

Shorty
March 7, 2013 4:24 pm
Seems valid. Never had this requirement myself, but now that I think about it your function is a good way to do it. Well done,sir! :)
Reply
Vishal
March 7, 2013 5:04 pm
જા રે બાવા બારેજા ……..
Reply
Agustin
March 7, 2013 6:10 pm
Nice
Reply
Eduard Uta
March 7, 2013 6:28 pm
Hi,
Definitely the REVERSE function does the job much easier and faster but just for fun I give you an alternative :D
declare @PalinString VARCHAR(256) = ‘Was it a car or a cat I saw’
declare @wlen int
declare @answer varchar(100)
set @PalinString = REPLACE(@PalinString, ‘ ‘, ”)
set @wlen = LEN(@PalinString)
if @wlen = 0
begin
set @answer = ‘Empty. Please add a string’
end
else if @wlen = 1
begin
set @answer = ‘One character => Palindrome’
end
else
begin
while (@wlen > 1)
begin
if LEFT(@PalinString, 1) = RIGHT(@PalinString, 1)
begin
set @PalinString = LEFT(@PalinString, LEN(@PalinString)-1)
set @PalinString = RIGHT(@PalinString, LEN(@PalinString)-1)
set @wlen = LEN(@PalinString)
set @answer = ‘Palindrome’
end
else
begin
set @answer = ‘Not Palindrome’
set @wlen = 0
end
end
end
print ‘The String is ‘ + @answer
select Answer = @answer
go
Reply
Eduard Uta
March 7, 2013 6:31 pm
Hi,
Definitely the REVERSE function does the job much easier but just for fun
here is an alternative for this “challenge”:
declare @PalinString VARCHAR(256) = ‘Was it a car or a cat I saw’
declare @wlen int
declare @answer varchar(100)
set @PalinString = REPLACE(@PalinString, ‘ ‘, ”)
set @wlen = LEN(@PalinString)
if @wlen = 0
begin
set @answer = ‘Empty. Please add a string’
end
else if @wlen = 1
begin
set @answer = ‘One character => Palindrome’
end
else
begin
while (@wlen > 1)
begin
if LEFT(@PalinString, 1) = RIGHT(@PalinString, 1)
begin
set @PalinString = LEFT(@PalinString, LEN(@PalinString)-1)
set @PalinString = RIGHT(@PalinString, LEN(@PalinString)-1)
set @wlen = LEN(@PalinString)
set @answer = ‘Palindrome’
end
else
begin
set @answer = ‘Not Palindrome’
set @wlen = 0
end
end
end
print ‘The String is ‘ + @answer
select Answer = @answer
go
Reply
Fabrizio Faleni
March 7, 2013 7:01 pm
Thanks Pinal!
Not only you’ll have to remove punctuation marks but also remove accents in all the languages that use them (which are a lot!). For example in this French palindrome “Ainsi Anaïs nia” the umlaut should be removed, such as in the Italian Palindrome “A Natale è la tana” should the grave accent. Do you have a smart solution for this too?
Kind regards!
Reply
Sanjay Monpara
March 7, 2013 8:19 pm
DECLARE @PalinString VARCHAR(256)
SET @PalinString = ‘ટેબલ’
SELECT CASE WHEN REPLACE(@PalinString, ‘ ‘, ”) = REVERSE(REPLACE(@PalinString, ‘ ‘, ”))
THEN ‘Palindrome’
ELSE ‘Not Palindrome’ END AS [Answer]
GO
This is showing ‘ટેબલ’ is Palindrome
because output of query,
select REPLACE(‘ટેબલ’, ‘ ‘, ”) , REVERSE(REPLACE(‘ટેબલ’, ‘ ‘, ”))
is
???? ????
Reply
- CHANDU
  March 8, 2013 4:04 pm
  Hi Sanjay,
  For that you have to use NVARCHAR…
  See this
  select REPLACE(N’ટેબલ’, ‘ ‘, ”) , REVERSE(REPLACE(N’ટેબલ’, ‘ ‘, ”))
  Reply
- kumarbabu
  May 22, 2013 11:32 am
  declare @str varchar(20) set @str=’kook’
  select case when replace(@str,’ ‘,”)=reverse(replace(@str,’ ‘ ,”))
  then ‘palindrome’
  else ‘not palindrome’
  end as result
  this will work
  Reply
harsh
March 8, 2013 9:56 am
REVERSE & Removing spaces is a better option. I have written one solution, which will consider only alphabets & digits . So, it will ignore space,exclamation mark ,comma etc.
DECLARE @PalinString VARCHAR(256)
SET @PalinString= ‘A man, a plan, a canal – Panama!’
DECLARE @I INT
DECLARE @J INT
SET @I=1
SET @J= LEN(@PalinString)
DECLARE @RESULT AS VARCHAR(20)
SET @RESULT=’Palindrome’
DECLARE @A INT
DECLARE @B INT
WHILE (@I=48 AND @A=65 AND @A=48 AND @B=65 AND @B<= 90))
BEGIN
SET @J=@J-1
CONTINUE
END
IF @A!=@B
BEGIN
SET @RESULT= 'Not Palindrome'
BREAK
END
SET @I=@I+1
SET @J=@J-1
END
PRINT @RESULT
–Result is Palindrome
Reply
- harsh
  March 8, 2013 6:13 pm
  My earlier solution was not properly posted due to greater than sign.
  DECLARE @PalinString VARCHAR(256)
  SET @PalinString= ‘A man, a plan, a canal – Panama!’
  DECLARE @I INT
  DECLARE @J INT
  SET @I=1
  SET @J= LEN(@PalinString)
  DECLARE @RESULT AS VARCHAR(20)
  SET @RESULT=’Palindrome’
  DECLARE @A INT
  DECLARE @B INT
  WHILE (@J>@I)
  BEGIN
  SET @A=ASCII(UPPER(SUBSTRING(@PalinString,@I,1)))
  SET @B=ASCII(UPPER(SUBSTRING(@PalinString,@J,1)))
  IF NOT((@A>=48 AND 57>=@A) OR (@A>=65 AND 90>=@A))
  BEGIN
  SET @I=@I+1
  CONTINUE
  END
  IF NOT((@B>=48 AND 57>=@B) OR (@B>=65 AND 90>=@B))
  BEGIN
  SET @J=@J-1
  CONTINUE
  END
  IF @A!=@B
  BEGIN
  SET @RESULT= ‘Not Palindrome’
  BREAK
  END
  SET @I=@I+1
  SET @J=@J-1
  END
  PRINT @RESULT
  Reply
Marcus
March 7, 2014 5:30 pm
You could create a function and declare an input variable as a varchar(), and then do an IF function to determine if the word or phrase is a palindrome, if it is return a value and vice versa, like this:
use tempdb
go
create function dbo.fnpalindrome (@Input1 varchar(30))
returns varchar(30)
As
begin
if @Input1 = reverse(@Input1)
select @Input1 + ‘ is a palindrome’
else
select @Input1 + ‘ is not a palindrome’
end
Once this function has been created you can then call on it any time, exec dbo.fnpalindrome for example.
Hope this gives a different way to think about it! :)
Reply
- Marcus
  March 7, 2014 5:31 pm
  Sorry it would be exec dbo.fnpalindrome(‘palindrome here’)
  Reply