July 30, 2012 at 10:44 am
Will people kindly share practical applications of REVERSE sql function? I do not see any posts on this forum addressing it and am curious.
Thanks in advance.
--Quote me
July 30, 2012 at 10:46 am
They must have been thinking of something when they added it. Don't know what though...
July 30, 2012 at 10:46 am
I've used it to parse out address fields that have city, state and zip in one field. It's usually easier to look through it backwards with the idea that everything up to the first space will be the zip code, and going from there.
July 30, 2012 at 10:51 am
I use it mostly to parse out the file names from a long file path. Example:
DECLARE @FilePath VARCHAR(100)
SET @FilePath = 'C:\Users\abcd\AppData\Local\Temp\Temp.txt'
SELECT REVERSE(LEFT ( REVERSE(@FilePath) , CHARINDEX('\',REVERSE(@FilePath))))
Instead of finding the last backslash throu string splitters, i used combination of functions along with REVERSE to do the task..
July 30, 2012 at 11:26 am
ColdCoffee (7/30/2012)
I use it mostly to parse out the file names from a long file path. Example:
DECLARE @FilePath VARCHAR(100)SET @FilePath = 'C:\Users\abcd\AppData\Local\Temp\Temp.txt'
SELECT REVERSE(LEFT ( REVERSE(@FilePath) , CHARINDEX('\',REVERSE(@FilePath))))
Instead of finding the last backslash throu string splitters, i used combination of functions along with REVERSE to do the task..
Generalisation - sometimes you'll find that a certain character in a string is repeated several times, and you want to extract the chunk of the string between the last occurrence of this character and the end of the string.
Without REVERSE(), this would be quite involved. ColdCoffee's solution shows how easy it is with REVERSE().
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 30, 2012 at 11:36 am
You can use it to check if 2 phrases are palindromes.
Just kidding.
July 30, 2012 at 11:56 am
OK, so you reverse the expression, find the character you want, perform the operation, and then reverse the expression again to aright it.
I have seen such situations arise, I now realize. Thank you CC and everyone for the responses.
--Quote me
July 30, 2012 at 12:12 pm
Luis Cazares (7/30/2012)
You can use it to check if 2 phrases are palindromes.Just kidding.
create function IsPalindrome(@String1 varchar(100), @String2 varchar(100))
returns table
return
(
select Case when @String1 = REVERSE(@String2) then 1 else 0 end as IsPalindrome
)
go
with cte (string1, string2)
as
(
Select 'racecar', 'racecar' union all
select 'racetruck', 'racetruck'
)
select *
from cte
cross apply dbo.IsPalindrome(string1, string2)
😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2012 at 12:33 pm
Thanks Sean, but I was joking with this because I'm not sure palindromes count as a "practical application".
July 30, 2012 at 12:43 pm
when I said thanks to you, Luis, it was for the relationship advice, not the palindrome idea.
--Quote me
July 30, 2012 at 12:56 pm
I've had a few cases where a lot of the searches were basically "ends with" searches. The best way to implement that is using a persisted computed column with an index on it, basically containing the REVERSE of the original string.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 30, 2012 at 1:22 pm
LEN(Reverse(<string>) is a quick and dirty way to find the length of a string INCLUDING trailing whitespace.
July 30, 2012 at 1:28 pm
sestell1 (7/30/2012)
LEN(Reverse(<string>) is a quick and dirty way to find the length of a string INCLUDING trailing whitespace.
Instead, you can use DATALENGTH function to get the length, inlcuding white spaces
DECLARE @FilePath VARCHAR(100)
SET @FilePath = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '
SELECT LEN(REVERSE(@FilePath)) [LenRevrs]
,DATALENGTH(@FilePath) [Datalength]
July 30, 2012 at 1:44 pm
Luis Cazares (7/30/2012)
Thanks Sean, but I was joking with this because I'm not sure palindromes count as a "practical application".
Well of course but it was rather fun to write in between a few other tasks. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2012 at 1:55 pm
ColdCoffee (7/30/2012)
sestell1 (7/30/2012)
LEN(Reverse(<string>) is a quick and dirty way to find the length of a string INCLUDING trailing whitespace.Instead, you can use DATALENGTH function to get the length, inlcuding white spaces
That's true, unless you're using Unicode strings.
DECLARE @FilePath VARCHAR(100),
@FilePath2 NVARCHAR(100)
SET @FilePath = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '
SET @FilePath2 = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ '
SELECT LEN(REVERSE(@FilePath)) [LenRevrs]
,DATALENGTH(@FilePath) [Datalength]
,LEN(REVERSE(@FilePath2)) [LenRevrs2]
,DATALENGTH(@FilePath2) [Datalength2]
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply