practical application of REVERSE function?

  • 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

  • They must have been thinking of something when they added it. Don't know what though...

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

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

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • You can use it to check if 2 phrases are palindromes.

    Just kidding.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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/

  • Thanks Sean, but I was joking with this because I'm not sure palindromes count as a "practical application".

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • when I said thanks to you, Luis, it was for the relationship advice, not the palindrome idea.

    --Quote me

  • 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?

  • LEN(Reverse(<string>) is a quick and dirty way to find the length of a string INCLUDING trailing whitespace.

  • 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]

  • 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/

  • 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]

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply