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: Yesterday @ 5:36 PM
Points: 5,470, Visits: 23,487
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


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:39 AM
Points: 673, Visits: 1,546
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
--
There are only 10 types of people in the world, those who understand binary, and those who don't.

Note: (as of now) only.. 1 and 4 applies (i am on my way...)
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 01, 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: Thursday, April 10, 2014 7:08 AM
Points: 3,448, Visits: 4,406
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:16 AM
Points: 2,826, Visits: 2,836
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 @ 3:19 AM
Points: 12,205, Visits: 9,163
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:32 PM
Points: 5,794, Visits: 8,006
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


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 @ 5:25 AM
Points: 3,683, Visits: 4,817
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