![]() ![]() It's just a matter of 3 nested REPLACE functions to handle ANY number of spaces to accomplish the puzzle we solved above. the "O"s represent unmodified spaces and the "X"s represent spaces changed to some "unlikely character" like a special non printable, almost non type-able character like ASCII 7 (the "Bell" character). and that leaves us with just singles spaces everywhere.Īgain. STEP 2 is to replace all occurrences of "XO" with NOTHING. When we replace all pairs of space "OO" with "OX", we get the following In this case, I'll use the visible character of "X" (which isn't unlikely but serves this visual example) to represent a space that has been changed in a pair of spaces. ![]() This is done by modifying the second space in each pair of spaces to be an "unlikely" character. I'll also state that the goal is to remove the extra spaces without making the original string any larger in the process because it may already be as large as it can be for the given datatype. Remember the goal is to convert all of those sets of spaces to just a single space without the use of RBAR even if the RBAR is through the simple use of a UDF. consider the following sets of spaces, please. I'll use the letter "O" to act as a "visible" space so you can see what I'm talking about. You can't make the data any bigger during the process because it might get too big for VARCHAR(8000) (or whatever size the column is) or you can't use VARCHAR(MAX) because you're using SQL Server 2000. The goal is to convert the sections of multiple spaces of unknown length to a single space each as quickly as possible. SELECT 'This, that, and the other thing.' UNION ALL SELECT ' This has multiple unknown spaces in it. = Create and populate a test table.ĭECLARE TABLE(OriginalString VARCHAR(8000)) You have a column of data that looks something like the following. This article explains the "puzzle solving" behind a common set based method to replace unknown numbers of adjacent spaces with a single space by establishing and replacing simple patterns in a set based fashion. The truth is that you don't need the RBAR of a User Defined Function at all. If you Google the problem, you find that most folks still resort to While Loops in functions or maybe even a Tally table or (ugh!) XML in a function to solve this seemingly complex problem. Replacing multiple spaces with a single space is an old problem. The the following link to the post in the discussion where Paul White demonstrates that not-so-little nuance:īe you Neophyte or "Ninja", thanks for being a part of this community, folks. ![]() One of the things that came out of the discussion is the fact that the default collation can make a huge impact on performance. All in all, the discussion makes for an incredible learning experience which is typical of the amazing community of professional people we've all grown to know and love here at SSC. Some folks even took the time to create and post some CLRs to solve the same problem. The discussion that followed this article is also fascinating and a large number of people took part in some rather wonderful testing. If you prefer to "cut to the chase", that method can be found in a post in the discussion for this article at the following URL: Although this article is still an interesting read (I left everything after the "Introduction" heading as it originally was), I no longer recommend the method in this article because it has been soundly beaten (more than 6 times faster) for performance by another T-SQL method as demonstrated by Michael Meierruth. As with anything else, improvements can be made to code and the subject of this article is no exception. This article was originally published on November 16th, 2009. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |