Published on

December 27, 2015

Exploring SQL Server: Reversing a String Without Using the Reverse Function

Last week, I had the opportunity to attend an interview where one of my co-interviewers asked a coding question related to SQL Server. The question challenged the candidate to write a User Defined Function (UDF) that could generate a script in reverse order without using the built-in reverse function. I was impressed by the candidate’s ability to successfully solve the problem, although they did stumble on one condition where they inadvertently used the reverse function.

Let’s take a look at the question and the candidate’s answer:

CREATE FUNCTION UDF_ReverseString (
    @StringToReverse VARCHAR(8000),
    @StartPosition INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
    IF (@StartPosition <= 0) OR (@StartPosition > LEN(@StringToReverse))
        RETURN (REVERSE(@StringToReverse))
    
    RETURN (
        STUFF(
            @StringToReverse,
            @StartPosition,
            LEN(@StringToReverse) - @StartPosition + 1,
            REVERSE(SUBSTRING(@StringToReverse, @StartPosition, LEN(@StringToReverse) - @StartPosition + 1))
        )
    )
END
GO

The candidate’s solution involved creating a UDF called UDF_ReverseString. This function takes two parameters: the string to be reversed (@StringToReverse) and the starting position (@StartPosition) from which the string should be reversed.

The function first checks if the @StartPosition is less than or equal to 0 or greater than the length of the @StringToReverse. If this condition is true, the function simply returns the reversed string using the built-in reverse function.

However, if the @StartPosition is within a valid range, the function uses the STUFF function to replace a portion of the @StringToReverse with the reversed substring. The STUFF function takes four parameters: the string to modify, the starting position, the number of characters to replace, and the replacement string.

In this case, the function replaces a portion of the @StringToReverse starting from the @StartPosition with the reversed substring obtained using the REVERSE and SUBSTRING functions.

What impressed me the most about this solution was its flexibility. The candidate’s UDF allowed for specifying the starting position from which the string should be reversed. Let’s see some examples:

-- Reversing the string from the third position
SELECT dbo.UDF_ReverseString('forward string', 3)
-- Result: forgnirts draw

-- Reversing the entire string by passing 0 as the beginning character
SELECT dbo.UDF_ReverseString('forward string', 0)
-- Result: gnirts drawrof

-- Reversing the entire string by passing a negative number as the beginning character
SELECT dbo.UDF_ReverseString('forward string', -9)
-- Result: gnirts drawrof

-- Reversing the entire string by passing a larger number than the string length as the beginning character
SELECT dbo.UDF_ReverseString('forward string', 900)
-- Result: gnirts drawrof

As you can see, the UDF successfully reverses the string based on the specified starting position, even when the starting position is outside the valid range of the string length.

In conclusion, the candidate’s solution to the coding question demonstrated their understanding of SQL Server functions and their ability to think creatively to solve problems. The UDF they created allows for flexible string reversal based on the starting position. This example serves as a reminder of the power and versatility of SQL Server and the endless possibilities it offers for data manipulation.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.