SQL SERVER - User Defined Functions (UDF) to Reverse String - UDF_ReverseString

May 1, 2007 by pinaldave

UDF_ReverseString
UDF_ReverseString User Defined Functions returns the Reversed String starting from certain position.
First parameters takes the string to be reversed.
Second parameters takes the position from where the string starts reversing.

Script of UDF_ReverseString function to return Reverse String.

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

Usage of above UDF_ReverseString:

Reversing the string from third position

SELECT dbo.UDF_ReverseString('forward string',3)

Results Set : forgnirts draw

Reversing the entire string passing 0 as beginning character

SELECT dbo.UDF_ReverseString('forward string',0)

Results Set : gnirts drawrof

Reversing the entire string passing negative number as beginning character

SELECT dbo.UDF_ReverseString('forward string',-9)

Results Set : gnirts drawrof

Reversing the entire string passing larger number than string length as beginning character

SELECT dbo.UDF_ReverseString('forward string',900)

Results Set : gnirts drawrof

SQL SERVER has REVERSE Function which is used in UDF above.
If you have to reverse complete string. I would rather suggest use following script rather than using the UDF above. The purpose of the UDF is to reverse the string from certain positioned characters only.

Example for REVERSE function in SQL SERVER 2005 from BOL:

USE AdventureWorks;
GO
SELECT FirstName, REVERSE(FirstName) AS REVERSE
    FROM Person.Contact
    WHERE ContactID < 5
    ORDER BY FirstName;
GO


FirstName      Reverse
-------------- --------------
Catherine      enirehtaC
Gustavo        ovatsuG
Humberto       otrebmuH
Kim            miK
(4 row(s) affected)

Reference : Pinal Dave (http://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 9 Comments

9 Responses to “SQL SERVER - User Defined Functions (UDF) to Reverse String - UDF_ReverseString”

on May 31, 2007 at 10:00 am Mei

Hi
can you help?
i need to get a segment of string from a string, using sql
( using SUBSTRING ?)

lets say that this is my string (ofcourse its yours)

“If you have to reverse “complete” string. I would rather suggest use following script rather than using the UDF above. The purpose of the UDF is to reverse”

my search looks for the word “complete” inside the string
but i need it to return a string that contains ten words before “complete” and ten words after “complete”

this is my sql:

SELECT Title, URL, LEFT(News, CHARINDEX(’ ‘, News, 10)) AS ShortDescr
FROM MyTable
WHERE FREETEXT(News, ‘complete’)

Thanks in advance
Mei
Israel
on June 7, 2007 at 4:31 am Sankoch

I have a problm

In Sql Server 2005 i have a colum which contain Varchar(50) datatype but data within colum is numeric.
How can i convert it into a numeric value in my sql statemrnt in which i want the average value of column?
on June 7, 2007 at 6:05 am pinaldave

Hi Sankoch,

SELECT AVG(CAST(yourvarcharcol AS INT))
FROM yourTable.

Kind Regards,
Pinal Dave (http://www.SQLAuthority.com)
on July 9, 2007 at 11:02 pm Praveen Kumar

I am facing one problem. My requirement is to reverse the string like this. str = ‘ab’,'bc’,'cd’,'de’ i want to get the output like ‘de’,'cd’,'bc’,'ab’

could u help me out to achieve the desire result.

thanx a lot in advance..

Praveen
on January 4, 2008 at 4:04 pm Paresh

Hi pinal..

How can i export data in execel file in predefined excel format…

Example suppose one excel file like..

‘Report of newmwmber os year 2000 are following’ — this is fix as header (for every ouput)

field name —-these are fields name
data — -these are data
.
.
.
.
.

so, hearder is not in sql data…

i want this solution…
on April 22, 2008 at 6:28 pm Anupama

Good example
on April 24, 2008 at 12:38 pm sathish

Hi,
I sathish can u tell difference between the predefined & userdefined in the stored procedured sql server 2005
on April 24, 2008 at 12:39 pm sathish

with some examples of the diffence

Regards,
sathish
on April 29, 2008 at 12:13 pm Bobby

Hi Pinal,

Please solve my problem.
I have fired 1 ‘UPDATE” query which updated some thousand plus records in the table which I never wanted. This is because I had forgotten to mention the ‘where” clause in the query so it the entire record.
Now I want to know is there some way in which I can get original records.
I had run the query without ‘BEGIN TRAN’ so ROLLBACK command is also not working.

Thanks,
Bobby