November 7, 2013 at 12:09 pm
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
November 7, 2013 at 12:38 pm
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 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/
November 7, 2013 at 12:54 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply