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

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.

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
Previous Post
SQL SERVER – Watch Four Efficiency Tricks in SQL Server In Sixty Seconds – Subscribe for SQL Learning Videos
Next Post
SQL SERVER – Tricky Question – What is the Default Size of the Database

Related Posts

13 Comments. Leave new

  • 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
  • જા રે બાવા બારેજા ……..

    Reply
  • Nice

    Reply
  • 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
  • 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
    • Hi Sanjay,

      For that you have to use NVARCHAR…
      See this
      select REPLACE(N’ટેબલ’, ‘ ‘, ”) , REVERSE(REPLACE(N’ટેબલ’, ‘ ‘, ”))

      Reply
    • 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
  • 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
    • 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
  • 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

Leave a Reply

Menu
Exit mobile version