Bonski's Box

Trimming that "untrimmable space"

UPDATE: It seems Community Server stripped the actual CHAR(160) in @someStringWithPad, just so that it will be contrived append  CHAR(160) at the  @someStringWithPad variable. ;)

Ever experienced trying the following code to trim a seemingly "space" padded string?

DECLARE @someStringWithPad VARCHAR(25)   

SET @someStringWithPad = 'space the final frontier '
SET @someStringWithPad = RTRIM(@someStringWithPad)   

IF  LEN(@someStringWithPad) > 24     
     PRINT 'ASSERT: expected 24 characters but was ' + CAST(LEN(@someStringWithPad) AS VARCHAR(3))     

Guess what will be printed? It will be the ASSERT message. Now what gives here? We've already RTRIMed the string, trying LTRIM(RTRIM()) won't help either. Doing a REPLACE(@someStringWithPad, SPACE(1),'') , doesn't help either.

Try doing the following:

SELECT ASCII(RIGHT(@someStringWithPad,1))

What do you get? It won't be CHAR(32) for space, it will be CHAR(160) for a Non-breaking Space.

So the fix is to issue a REPLACE(@someStringWithPad, CHAR(160), '') a little tricky if you ask me, and hopefully will save you some headscratching as to why your INNER JOIN or WHERE clauses doesn't match those that is padded with CHAR(160).

In SSMS 2005, set the "Include IF NOT EXISTS clause" to false ALWAYS

This is a frustrating UI experience, setting the "Include IF NOT EXISTS clause" to TRUE in the scripting options of SSMS 2005 results in the following when using a Modify or any scripting options:

While it does wrap an IF NOT EXISTS statement(a safety check and a  must) it deserializes the stored procedure to be executed inside  an sp_executesql command. This sucks, since you have to manually remove all of the text before the ALTER and text after the last delimiter everytime you're going to modify something, worse if the sql contains literal strings you have to manually search/replace all the single quote delimeter.  I had to uninstall SSMSToolpack (great add-in) which I thought was the culprit, but the thing still remains.

Good thing I was helped by Tibor Karzi and pointed me to disable that setting. This UX change was already logged as an issue way back in 2007, it was reported in SP2 mine is already in SP3. I'm not sure if this still happens in SSMS 2008 though.

Hope this helps