SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding REVERSE(STUFF(REVERSE... code


Understanding REVERSE(STUFF(REVERSE... code

Author
Message
Patrick Dooley
Patrick Dooley
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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


Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59965 Visits: 17949
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.

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)
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40334 Visits: 19807
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.
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search