Replacing multiple occurences of space with one space in a string

Given string :  THIS                IS                A                 BAD                 STRING
Out put string : THIS IS A BAD STRING

The Logic
-----------------

• In the given string replcae the space with a token (e,g. ~ in this case) and a space  e.g. '~ '
• Repace the space and token in the string result from step 1 with  an empty string.' ~'
• Replace the result of the above atring with a token and the space

DECLARE
@str AS VARCHAR(1000) = 'THIS IS A BAD STRING',@Token AS VARCHAR(1) = '~'

SELECT REPLACE(REPLACE(REPLACE(@str,' ','~ '),' ~',''),'~',' ')

Counting the occurence of a substring within a string

Today I learnt a special techinque from SQL Server magazine to find the total number of substrings within a string. The logic is so simple that I am surprised why I never used it.
E.g. abcdballmnopballqrstball
We need to find the total number of occurences of the substring "ball" in the given string.
 Logic
--------------
Find the substring ball and replace it with ''   e.g.abcdmnopqrst
Find the length of the new string                   12
Subtract the new length from the lenth of the original string  24-12 = 12
Divide the new number with the length of the substring   12/4 = 3

Here is the script - taken form the November issue of SQL server magazine

DECLARE
@str       AS VARCHAR(1000) = ' abcdballmnopballqrstball ';
@substr  AS  VARCHAR(1000) = 'ball';


SELECT  LEN(@str) - LEN(REPLACE(@str,(@substr,''>/LEN(@substr)

This is so awesome!

COALESCE - Selecting each record with all not nul values

I had three rows of data that had the same acount number but differnt variation in the three columns. I had to select each account with not null column . This was achieved by the following...

My data
create table #tmpcol(c219 int, c220 int,c221 int)
INSERT INTO #tmpcol
SELECT null,220,null
INSERT INTO #tmpcol
SELECT 219,null,null
INSERT INTO #tmpcol
SELECT null,null,221


select top 1 * from #tmpcolorderby coalesce(100*c219,10*c220,c221) desc