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 123»»»

STUFF - 1 Expand / Collapse
Author
Message
Posted Thursday, November 22, 2012 8:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
Comments posted to this topic are about the item STUFF - 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1388011
Posted Thursday, November 22, 2012 10:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:02 PM
Points: 998, Visits: 2,280
nice :) thank you for the question

(though it was simple and straight question, not sure why i kept occupied myself thinking as it was a trick question... )


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1388026
Posted Thursday, November 22, 2012 10:54 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
Thanks ,good question.. learn first time about STUFF and then answered it

-----------------------------------------------------------------------------
संकेत कोकणे
Post #1388034
Posted Thursday, November 22, 2012 10:59 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Another question about different stuff
An interesting example of inserting one string into another. Never thought it could be done that way. I always did it using something like
SELECT LEFT(@string1, 10) + @Stuffing + RIGHT(@string1, LEN(@string1) - 10)

Post #1388035
Posted Thursday, November 22, 2012 11:07 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:45 AM
Points: 616, Visits: 97
+1
Post #1388041
Posted Friday, November 23, 2012 12:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:53 AM
Points: 3,133, Visits: 3,402
Couldn't work out if it was a trick question or not - decided it wasn't

Thanks for the point and the question Ron


-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1388066
Posted Friday, November 23, 2012 12:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,239, Visits: 11,018
Nice question to end the week. Thanks Ron!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1388067
Posted Friday, November 23, 2012 1:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
vk-kirov (11/22/2012)
Another question about different stuff
An interesting example of inserting one string into another. Never thought it could be done that way. I always did it using something like
SELECT LEFT(@string1, 10) + @Stuffing + RIGHT(@string1, LEN(@string1) - 10)


STUFF is a very powerful function that can be used for many things, but unfortunately is not very well known. I have seen people struggle with RIGHT and REVERSE or SUBSTRING and LENGTH to cut off the first three characters of a string, instead of simply using STUFF(@string, 1, 3, '').

Ron, thanks for this question. I hope it helps spread the knowledge about STUFF.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1388071
Posted Friday, November 23, 2012 1:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 4,013, Visits: 5,306
Good question to end the week on, thanks Ron

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1388075
Posted Friday, November 23, 2012 2:08 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
Thanks for a good question, I know about STUFF for replacing parts of strings but thought the '0' for the length parameter formed part of a trick question (i.e. insert no characters from @stuffing rather than delete no characters from @string1) so selected 16, not 46.

Then after getting it wrong :-( and researching the question I found this comment from Zafar Yousafi in BOL:


At first sight it might not be clear directly how to insert a character (or a string) instead of substituting characters in the original string.


To insert a string, without replacing/substituting characters in the original string you need to specify a length of 0 (zero).
SELECT STUFF('abcghi', 4, 0, 'DEF');
-- this results in 'abcDEFghi'.


So, learned something new today - thanks!


---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1388079
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse