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 (https://blog.sqlauthority.com)
16 Comments. Leave new
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
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?
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
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…
Good example
Hi,
I sathish can u tell difference between the predefined & userdefined in the stored procedured sql server 2005
with some examples of the diffence
Regards,
sathish
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
Did you have a latest backup taken prior to updatation?
If so restore it to get original data
Oh just restore db..
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’
Use instead of reversing just replace function would be better or stuff function.
My question is how to reverse sentence not words, for eg. I am Bharat. I want to reverse it as Bharat am I.
–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
hey pinal
i want to find a sub string using like wildcards can you provide me a solution ?