SQL Reverse String

How to write a SQL Server Query to reverse every word present in a string with an example? The reverse string is one of the standard Interview Questions you might face interviews.

SQL Reverse String Word by Word Example

The SUBSTRING function allows you to extract and display the part of a string. In this SQL Server example, we show you how to Select the string before the space symbol and reverse those words.

DECLARE @Source VARCHAR(MAX)
DECLARE @Destination VARCHAR(MAX)
DECLARE @Length INT 

SET @Source = 'Welcome to SQL Server Tutorial Service at Tutorial Gateway'
SET @Destination = ''

WHILE LEN(@Source) > 0
  BEGIN
    IF CHARINDEX(' ', @Source) > 0
    BEGIN
	SET @Destination = SUBSTRING(@Source, 0, CHARINDEX(' ', @Source)) + ' ' + @Destination
	SET @Source = LTRIM(RTRIM(SUBSTRING(@Source, CHARINDEX(' ', @Source) + 1, LEN(@Source))))
    END
    ELSE
    BEGIN
	SET @Destination = @Source + ' ' + @Destination
	SET @Source = ''
    END
  END

SELECT @Destination
SQL Reverse String Words 1

First, we declared three variables using the DECLARE statement. Next, we used the WHILE Loop to iterate every character present in the @Source. For the condition inside the SQL Server while loop, we used the LEN Function to check whether the length of the source is greater than 0 or not.

Within the while loop of the SQL Server reverse string example, we used the SUBSTRING Function to set the @Destination value. And it allows three parameters: Source, start point, and endpoint. Here we assigned the

  • Source as @Source, or you can use Column Name
  • Next, we used the starting position as 0.
  • Next, we used the CHARINDEX Function to find the ‘ ‘ So that the endpoint will be before the space.
  • Lastly, we are adding this to @Destination, which is empty at the start of the loop.

To set the @Source value (it means to update the @source variable), we are using the SUBSTRING Function. Here we assigned the

  • Source as @Source, or you can use Column Name.
  • Next, we used the CHARINDEX Function to find the empty space, and then we added 1. so that the starting point would be after space.
  • Lastly, we used the LEN Function to specify the end value

From the above analysis, you can observe that,

  • SET @Destination means we are extracting the word starting at 0 indexes and up to empty space. That will be the first word.
  • Once we get our first word, we will remove that word from @Source using the SET @Source code.

SQL Reverse String Word by Word Example 2

Although the above example will return the result, we want to wrap the reverse string in the User Defined Function. I suggest you refer to the User Defined article for a better understanding.

CREATE FUNCTION [ReverseStringWords]
(
    @Source VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)  
BEGIN
         
   DECLARE @Destination VARCHAR(MAX)
   SET @Destination = '' 
    
   DECLARE @Length INT
     
   WHILE LEN(@Source) > 0
   BEGIN
      IF CHARINDEX(' ', @Source) > 0
      BEGIN
         SET @Destination = SUBSTRING(@Source, 0, CHARINDEX(' ', @Source)) + ' ' + @Destination
         SET @Source = LTRIM(RTRIM(SUBSTRING(@Source, CHARINDEX(' ', @Source) + 1, LEN(@Source))))
      END
      ELSE
      BEGIN
         SET @Destination = @Source + ' ' + @Destination
         SET @Source = ''
      END
   END          
   RETURN @Destination
END

We can find the user defined function in two ways. Please navigate to the Database that contains the function -> Programmability -> Scalar-valued Functions -> functionName. Next, right-click on the function name will open the context menu. Here, please choose the Script Function as -> Select To -> New Query Editor Window option.

SQL Reverse String Words 2

Or write the following code snippet to reverse a string.

-- Example

SELECT [dbo].[ReverseStringWords]('Welcome To SQL Server Tutorial Service at Tutorial Gateway')
GO
Reverse String Words 3