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. The palindrome 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]
GO

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 (http://blog.sqlauthority.com)

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

  1. 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! :)

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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!

    Like

  5. 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
    ???? ????

    Like

    • Hi Sanjay,

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

      Like

    • 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

      Like

  6. 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

    Like

    • 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

      Like

  7. 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! :)

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s