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

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://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – User Defined Functions (UDF) to Reverse String – UDF_ReverseString

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

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

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

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

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

  6. hi pinaldave,
    I’ve got a problem in reverse function
    i have a feild which has Nvarchar(50) type and contain both character and number, and only the number muse be reverse.
    for example ‘i am 01 years old’ i want to convert into
    ‘i am 10 years old’

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  8. –567 convert to 765
    –Without using Built-In-Function

    declare @FromNum int
    Set @FromNum =567
    Declare @FromNum2 int
    set @FromNum2 =56
    declare @var1 int,@var2 int,@var3 int
    declare @newvar1 int ,@newvar2 int

    set @var1 =(select LEFT(’567′,1))
    set @var2 =(select right(’56′,1))
    set @var3 =(select right(’567′,1))

    if @var1=@var3
    print ‘no change in order’
    begin
    if @var1 @var3

    set @newvar1 =@var3
    print @newvar1

    set @newvar1 =@var1
    end
    if @var3 @var1
    set @var3=@var1
    print @var2
    print @var3

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