In this article we will show you, How to write a SQL Query to reverse each and every word present in a string with example. This is one of the common SQL Interview Question you might face in the interviews.
SQL Reverse String Word by Word Example
The SQL SUBSTRING Function allows you to extract and display the part of a string. In this example we will show you, How to Select string before the space symbol.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- SQL Extract Domain From Email 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 |
OUTPUT: Let me show you the output of the statement
ANALYSIS
First we declared three variables using the DECLARE statement. Next we used the WHILE Loop to iterate each and every character present in the @Source. For the condition inside the while loop, we used the SQL LEN Function to check whether the length of the source is greater than 0 or not.
Within the while loop, we used the SQL SUBSTRING Function to set the @Destination value. And it allows three parameters and they are: Source, start point, end point. 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 SQL CHARINDEX Function to find the ‘ ‘. so that the end point will be before the space.
- Lastly, we are adding this to @Destination, and it is empty at the starting of the loop
To set the @Source value (it means update the @source variable) we are using the SQL SUBSTRING Function. Here we assigned the
- Source as @Source or you can use Column Name
- Next, we used the SQL CHARINDEX Function to find the empty space, and then we added 1. so that the starting point will be after the empty space.
- Lastly, we used the SQL 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 index and up to empty space. That will be the first word.
- Once we got 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 just want to wrap it in the User Defined Function. I suggest you to refer User Defined Function article for better understanding.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
-- SQL Query to Reverse String words USE [SQLTEST] GO 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 |
OUTPUT
We can find the user defined function in two ways. Please navigate to the Database that contains the function -> Programmability -> Scalar-valued Functions -> function name. Next, right click on the function will open the context menu. Here, please select the option as we shown below.
Or you can simply write the following SQL code snippet
|
1 2 3 4 5 |
USE [SQLTEST] GO SELECT [dbo].[ReverseStringWords]('Welcome To SQL Server Tutorial Service at Tutorial Gateway') GO |
And the Output is
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply