Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Understanding REVERSE(STUFF(REVERSE... code Expand / Collapse
Author
Message
Posted Thursday, November 7, 2013 12:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 6:16 AM
Points: 4, Visits: 6
Hello - I've got a lot of experience writing basic queries, but I've not come across the REVERSE(STUFF(REVERSE... functions, and I am encountering them fairly frequently in my client's code. I know that 'REVERSE' can take a string and reverse its order on output, and that STUFF allows one to place a string in the middle of another string and specify which parts of the string get replaced. However, seeing them together (as in the example code below), I am unsure what the English translation is. I would like to know what this snippet of code is actually doing and what is ultimately written out to the field 'AccountCode_Lkup_Error_Msg'. Thank you!

(REVERSE(STUFF(REVERSE(CASE WHEN S.Phase_Code IS NOT NULL AND S.ValidPhaseCode IS NULL THEN 'Phase value ' + S.Phase_Code + ' not present in TBL_ACCOUNTING_CODE table | ' ELSE '' END
+ CASE WHEN S.Member IS NOT NULL AND S.ValidMember IS NULL THEN Member value ' + S.Member + ' not present in TBL_ACCOUNTING_CODE table | ' ELSE '' END
+ CASE WHEN s.Participation IS NOT NULL AND S.ValidParticipation IS NULL THEN 'Participation value '+ S.Participation + ' not present in TBL_ACCOUNTING_CODE table | ' ELSE '' END), 1, 3, '')))
END
as AccountCode_LkUp_Error_Msg

Post #1512408
Posted Thursday, November 7, 2013 12:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
Patrick Dooley (11/7/2013)
Hello - I've got a lot of experience writing basic queries, but I've not come across the REVERSE(STUFF(REVERSE... functions, and I am encountering them fairly frequently in my client's code. I know that 'REVERSE' can take a string and reverse its order on output, and that STUFF allows one to place a string in the middle of another string and specify which parts of the string get replaced. However, seeing them together (as in the example code below), I am unsure what the English translation is. I would like to know what this snippet of code is actually doing and what is ultimately written out to the field 'AccountCode_Lkup_Error_Msg'. Thank you!

(REVERSE(STUFF(REVERSE(CASE WHEN S.Phase_Code IS NOT NULL AND S.ValidPhaseCode IS NULL THEN 'Phase value ' + S.Phase_Code + ' not present in TBL_ACCOUNTING_CODE table | ' ELSE '' END
+ CASE WHEN S.Member IS NOT NULL AND S.ValidMember IS NULL THEN Member value ' + S.Member + ' not present in TBL_ACCOUNTING_CODE table | ' ELSE '' END
+ CASE WHEN s.Participation IS NOT NULL AND S.ValidParticipation IS NULL THEN 'Participation value '+ S.Participation + ' not present in TBL_ACCOUNTING_CODE table | ' ELSE '' END), 1, 3, '')))
END
as AccountCode_LkUp_Error_Msg



Well whatever you posted actually doesn't work because there are some syntax issues around your strings. When trying to understand complex piece like this you have to find a way to make it easier to figure out. I did this to your code by removing all the case expressions and just making a big string.

Something like this:

SELECT Reverse(Stuff(Reverse(
'Phase value S.Phase_Code not present in TBL_ACCOUNTING_CODE table | '
+ 'Member value S.Member not present in TBL_ACCOUNTING_CODE table | '
+ 'Participation value S.Participation not present in TBL_ACCOUNTING_CODE table | '
), 1, 3, ''))
AS AccountCode_LkUp_Error_Msg
, 'Phase value S.Phase_Code not present in TBL_ACCOUNTING_CODE table | '
+ 'Member value S.Member not present in TBL_ACCOUNTING_CODE table | '
+ 'Participation value S.Participation not present in TBL_ACCOUNTING_CODE table | ' as OriginalString

Basically all this is doing is removing the last 3 characters from the string. It is not the most efficient way to do this because REVERSE is an expensive operation and this code does it twice.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1512417
Posted Thursday, November 7, 2013 12:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:04 PM
Points: 3,572, Visits: 8,008
Just to add on what Sean said, you could change those functions with a LEFT function.

WITH CTE(String) AS(
SELECT 'Some test string')
SELECT REVERSE(STUFF(REVERSE(String), 1, 3, '')) FROM CTE
UNION ALL
SELECT LEFT(String, LEN(String) - 3 ) FROM CTE




Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1512423
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse