Leveraging the “STUFF” Function for Enhanced Data Representation and String Manipulation

Question

Leveraging the “STUFF” Function for Enhanced Data Representation and String Manipulation

Ganesh Gopal Masti Jayaram

SSC Veteran

Points: 213
More actions
January 3, 2025 at 12:00 am

#4492885

Comments posted to this topic are about the item Leveraging the “STUFF” Function for Enhanced Data Representation and String Manipulation

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thomas Franz Hall of Fame Points: 3901 More actions · Answer 1

Someone seems to know, how to ask a KI for a full explanation with examples for an SQL function...

If this would be a manual written article, you would have realized / mentioned that for the most examples are much easier / better to read options available, as REPLACE() or TRANSLATE() or simple string CONCAT() (either with this function or a +) combined with LEFT() / SUBSTRING().

I agree, that STUFF() may have his use in some rare edge cases or when someone wants to show, that he have read the documentation :-), but in my whole ~20 years experience I've seen STUFF only once or twice outside of the old replacement of STRING_AGG() by combining it with FOR XML.

God is real, unless declared integer.

Snehalatha Mannar Old Hand Points: 315 More actions · Answer 2

Really a good article. Do you by any chance know if there is a comparable function that I can use in Snowflake SQL

Thomas Franz Hall of Fame Points: 3901 More actions · Answer 3

@Snehalatha Mannar: LEFT(), RIGHT(), SUBSTRING(), REPLACE(), CHARINDEX() and combining strings by adding them is part of every ANSI-SQL-Standard compatible language, so you can use them to do easily what STUFF() does (and often it is more straight forward)

This reply was modified 1 day, 16 hours ago by Thomas Franz.

God is real, unless declared integer.

Ganesh Gopal Masti Jayaram SSC Veteran Points: 213 More actions · Answer 4

Thomas Franz - With all due respect, every project and scenario is unique and we found this function to work the best based on our project context. You may refer to it if you have questions. I will rest my case with that 🙂 .

This reply was modified 1 day, 12 hours ago by Ganesh Gopal Masti Jayaram.

Robert Sterbal SSChampion Points: 11055 More actions · Answer 5

What was the unique context in your project?

412-977-3526 call/text

Ganesh Gopal Masti Jayaram SSC Veteran Points: 213 More actions · Answer 6

The context of the project was to build a report that would need us to concatenate values from various unique rows into a single string, with delimiters (like commas) and then to export the same to Excel macros as a single attribute into 1 single field. We found that no other function combines string concatenation and delimiter management as seamlessly as STUFF with FOR XML PATH. Hence we used this. While , i understand there might other approaches that can make this to also work, we found this useful.

Ganesh Gopal Masti Jayaram SSC Veteran Points: 213 More actions · Answer 7

Snehalatha - You can achieve similar functionality in SNOWFLAKE using a combination of SUBSTR, CONCAT, and REPLACE functions like what Thomas Franz already mentioned. Thank you

Sakthi S Newbie Points: 5 More actions · Answer 8

This article does an excellent job of explaining string concatenation in SQL, particularly the use of STUFF to remove unwanted delimiters like leading commas or semicolons. While there are many other built-in functions available, this technique truly stands out as one of the most powerful for handling string aggregation. It’s fantastic to see such useful functions being shared with the wider community.

priyankagunti Newbie Points: 8 More actions · Answer 9

One of the best articles that I have ever read. Thank you Ganesh.

Ross McMicken SSCarpal Tunnel Points: 4440 More actions · Answer 10

Snehalatha Mannar wrote:

Really a good article. Do you by any chance know if there is a comparable function that I can use in Snowflake SQL

Try the string insert command https://docs.snowflake.com/en/sql-reference/functions/insert

Thomas Franz Hall of Fame Points: 3901 More actions · Answer 11

Are now bots commenting and praising an AI generated article?

For the sake of completnes in SQL 2019 you can use STRING_AGG() to aggregate the text of multiple lines - something that was usually be done before by using FOR XML in combination with the STUFF function.

And to remove trailing delimiters you can use TRIM(TRAILING ', ;' FROM my.COLUMN) starting at SQL 2022 (or cloud).

PS: I don't say, that STUFF() is useless, but most of its use cases can be done otherwise and often shorter / easier:

-- Example 1: Inserting " OEM" after "Reseller"
-- Original with STUFF()
SELECT STUFF(Customer_Stage, CHARINDEX('Reseller', Customer_Stage) + LEN('Reseller'), 0, ' OEM')
-- shorter / easier to understand with a simple replace
SELECT REPLACE(Customer_Stage, 'Reseller', 'Reseller OEM')

-- Example 2: replacing a string - to be honest this example is very stupid (who does whole text replacements in da date by converting it into a string and back) and confirm my suspicions that the whole article is created by ChatGPT etc.
-- Original with STUFF()
SELECT CAST(STUFF(CONVERT(VARCHAR, Invoice_Date, 120), 1, 10, '2023-03-10') AS DATE)
-- there is no real meaningfull way to repeat this nonsense by replace, because it would look even more silly
SELECT CAST(REPLACE(CONVERT(VARCHAR, Invoice_Date, 120), CONVERT(VARCHAR, Invoice_Date, 120), '2023-03-10') AS DATE)
-- much better is to use a simple
SELECT '2023-03-10' AS invocice_date
-- or if you just want to replace a part of the date:
SELECT DATEFROMPARTS(YEAR(Invoice_Date) + 1, MONTH(Invoice_Date), 1) -- change it to first day of the month of the next year. Or you could use EOMONTH() (end of month) and / or DATEADD()

-- Example 3 - remove the @:
-- Original with STUFF()
SELECT STUFF(Customer_Stage, CHARINDEX('@', Customer_Stage))
-- easier with REPLACE()
SELECT REPLACE(Customer_Stage, '@', '')
-- exception: when you just want to replace the very first @ in the string, STUFF() may be handier / shorter because you need the calculation of its position just once (and even this could be precalculated in a subselect)
SELECT LEFT(Customer_Stage, CHARINDEX('@', Customer_Stage) - 1) + SUBSTRING(Customer_Stage, CHARINDEX('@', Customer_Stage) + 1, 8000)

-- Example 4 - Formating the phone number '1234567890'
-- Original with STUFF()
SELECT STUFF(STUFF(STUFF('1234567890', 7, 0, '-'), 4, 0, ') '), 1, 0, '(') AS Formatted_Phone
-- with CONCAT() - it may be longer but easier to understand than the nested STUFF() for the average developer, but personally I agree, that STUFF() could be an option in very special cases.
SELECT CONCAT('(', LEFT('1234567890', 3), ') ', SUBSTRING('1234567890', 4, 3), '-', SUBSTRING('1234567890', 7, 8000))

-- Example 5 - FOR XML
-- this is the only "real", often used use case in the past with the drawback of double reading the source table (since a and x are the same, which makes usually not much sense)
SELECT a.cus_id, STUFF(
        (SELECT DISTINCT ';' + Customer_Stage
         FROM CustomerDataStuff x
         WHERE x.Cus_ID = a.Cus_ID
         FOR XML PATH('')
        ), 
        1, 1, '') AS Stages
  FROM CustomerDataStuff a
-- today this would be done by STRING_AGG() with the drawback that it doesn't support DISTINCT so you may need to do it in a subselect
SELECT x.cus_id, STRING_AGG(Customer_Stage, ';') WITHIN GROUP (ORDER BY Customer_Stage) AS stages
  FROM CustomerDataStuff x
 GROUP BY x.cus_id

God is real, unless declared integer.