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 07, 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 07, 2013 12:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,990, Visits: 11,007
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 07, 2013 12:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 2,763, Visits: 5,908
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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