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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy