Using T-SQL to remove non-printable characters

 We frequently have a need to remove non-printable characters from text fields for export or printing.  Most often, this is the chars 9,10,or 13, but can frequently consist of other unicode characters.

Before I go on, let me say that I understand the whole idea of "printable" is dependant on what you mean by "print".  For simplicity, I am defining "printable" as anything in the base ASCII set (<128) that will actually display in the standard SQL Management Studio query results.  Therefore, everything else is "non-printable".  I realize there may be an exception or two, so I made sure to write code that was easily modifiable to include exceptions.

I first tried PATINDEX, but the pseudo-regex patterns can return somE wacky results, based on which default collation is being used.  It would also make the code harder to read and modify for someone not familiar with the PATINDEX flavor of regex.

So, here it is.  It is not the most efficient way, but certainly adequete, while being easy to modify.


DECLARE @I INT,
        @TEST VARCHAR(100)

SELECT @TEST='123' + CHAR(13) + '412' + CHAR(200) + '341', --testing string
       @I=0 --start at zero


WHILE @I<256 --check entire extended ascii set
BEGIN
   IF @I=33 SELECT @I=127 --this jumps over the range that I want to keep
   SELECT @TEST=REPLACE(@TEST, CHAR(@I), ' ') --this replaces the current char with a space
   SELECT @I=@I+1
END

SELECT @TEST


Another way to do this which allows better granular control over which characters you allow is this:


DECLARE @GOOD VARCHAR(256),
        @TEST VARCHAR(100),
@I INT

SELECT @GOOD='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()-_=+/?.>,<;:|~ ' + char(13) + char(10) + char(9)

SELECT @TEST='123' + CHAR(13) + '412' + CHAR(200) + '341', --testing string
@I=0
SELECT @TEST


WHILE @IBEGIN
IF PATINDEX('%' + SUBSTRING(@TEST,@I,1) + '%',@GOOD)=0
BEGIN
SELECT @TEST=REPLACE(@TEST,SUBSTRING(@TEST,@I,1),' ')
END

   SELECT @I=@I+1
END

SELECT @TEST

Did this help you?  If so, please leave a comment!

More adventures in T-SQL

I often see overly complicated where clauses in procedures or views that could have been simplified with a little trick I have been using for years.

Here is an example of a where clause which is hard to read, and therefore hard to debug and modify:

WHERE (SV00700.Service_Call_ID = @Service_Call_ID OR
@DocumentNumber <> '' OR
@BatchNum <> '') AND (SV00700.Call_Invoice_Number = @Call_Invoice_Number OR
@DocumentNumber <> '' OR
@BatchNum <> '') AND (@DocumentNumber = '' OR
@DocumentNumber = rtrim(SV00700.Call_Invoice_Number)) AND (@BatchNum = '' OR
SV00700.BACHNUMB = @BatchNum)

The intent here was to filter based on a combination of parameters.
The trick I like to use is simple:  Assume all parameters are empty and temporarily replace them with the values.  If the statement still looks complicated, you need to refactor.

In this case:
WHERE (SV00700.Service_Call_ID = '' OR

'' <> '' OR
'' <> '') AND (SV00700.Call_Invoice_Number = '' OR
'' <> '' OR
'' <> '') AND '' = '' OR
'' = rtrim(SV00700.Call_Invoice_Number)) AND '' = '' OR
SV00700.BACHNUMB = '' ) AND remit2.INTERID = rtrim(db_name())

Notice all those instances of '' <> ''.  That is what SQL is actually seeing at run-time if the parameter is an empty string. 

We can make this much easier to read by figuring out how to evaluate each parameter individually, rather than trying to evaluate all possible combinations.

Consider this:
WHERE SV00700.Service_Call_ID LIKE CASE RTRIM(@Service_Call_ID) WHEN '' THEN '%' ELSE RTRIM(@Service_Call_ID) END
  AND SV00700.Call_Invoice_Number LIKE CASE RTRIM(@Call_Invoice_Number) WHEN '' THEN '%' ELSE RTRIM(@Call_Invoice_Number) END
  AND SV00700.Call_Invoice_Number LIKE CASE RTRIM(@DocumentNumber) WHEN '' THEN '%' ELSE RTRIM(@DocumentNumber) END
  AND SV00700.BACHNUMB LIKE CASE RTRIM(@BatchNum) WHEN '' THEN '%' ELSE RTRIM(@BatchNum) END
It accomplishes the same thing, but is much easier to read.  Further, each parameter can be easily removed without reworking the entire statement.
 
When we apply the trick to it:
WHERE SV00700.Service_Call_ID LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END

AND SV00700.Call_Invoice_Number LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END
AND SV00700.Call_Invoice_Number LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END
AND SV00700.BACHNUMB LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END

It is still easy to read. 

I hopes this helps someone else to go forth and simplify.



Did this help you?  If so, please leave a comment!

Wentity tip

I was working with a wentity object (a custom data object).  I bound it to some controls on my form.
Later, when coding the logic to open the form, I passed in values that were set on some of these fields to pull up an initial record.  When testing, however, I found that when I entered and then left one of these fields, the values in the other 2 were cleared.  I could not find any of my code that was the culprit.  Then I began wondering if the wentity object was the culprit.

In deed it was.  Each time I refreshed the window, I newed up the wentity object.  This cleared the fields in the object, but not on the window.  However, whenever I did anything that called validation logic from the wentity object, the values were refreshed (and cleared)!

The fix was to manually set the values for these three variables in the wentity object each time I newed it.


Did this help you?  If so, please leave a comment!

Office 2010

I love these new ribbons in Office.

I created a custom ribbon and moved it to the top of the tab list show it is the default tab that shows.
It has my most commonly used tasks in outlook (some of which normally require significant digging to get to).

I exported it in case anyone else is interested in it.

The true beauty is that the exported file is XML..woot!

So I can post it here.  Just save it to a notepad file and give it the extension .exportedUI
Then right click on any tab in outlook and choose customize.  At the bottom of the window, choose Import/Export, then Import.  Browse to the file and import it.


Here is the xml for it:

<mso:cmd app="olkexplorer" dt="1" />
<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
<mso:ribbon>
<mso:qat/>
<mso:tabs>
<mso:tab id="mso_c1.27D5463" label="David's Tab" insertBeforeQ="mso:TabMail">
<mso:group id="mso_c2.27D5463" label=" " autoScale="true">
<mso:control idQ="mso:NewMailMessage" visible="true"/>
<mso:control idQ="mso:NewAppointment" visible="true"/>
<mso:control idQ="mso:RulesAndAlerts" visible="true"/>
<mso:control idQ="mso:OutOfOffice" visible="true"/>
<mso:control idQ="mso:AllModuleNameItems" visible="true"/>
</mso:group>
<mso:group id="mso_c1.288DC28" label=" " autoScale="true">
<mso:control idQ="mso:SendReceiveAll" visible="true"/>
<mso:control idQ="mso:Reply" visible="true"/>
<mso:control idQ="mso:ReplyAll" visible="true"/>
<mso:control idQ="mso:Forward" visible="true"/>
<mso:control idQ="mso:Delete" visible="true"/>
</mso:group>
<mso:group id="mso_c1.287D94B" label=" " autoScale="true">
<mso:control idQ="mso:MarkAsRead" visible="true"/>
<mso:control idQ="mso:MarkAsUnread" visible="true"/>
</mso:group>
<mso:group id="mso_c2.288638F" label=" " autoScale="true">
<mso:control idQ="mso:CheckForUpdates" imageMso="GreenBall" visible="true"/>
<mso:control idQ="mso:ShowInConversations" visible="true"/>
</mso:group>
</mso:tab>
</mso:tabs>
</mso:ribbon>
</mso:customUI>


Did this help you?  If so, please leave a comment!

More fun with SSRS

One of the nice things about Visual Studio is that you can set a default font for the controls and then as you drag them onto the form, the font is applied.

But what happens when you later want to adjust the default font size for the entire page, for instance?
Changes to the default do not flow down to the existing controls (Hint to MS, this would be a awesome feature to add!)

To handle this, you can open the report and choose View > Code, then do a find/replace on the elements I am targeting.  Don't try to scroll down and find the elements, since they are not necessarily listed in any logical order.

Someone should write a mass re-formatting tool for SRS that uses regex to find certain nodes and alter the structure for the new version.



Did this help you?  If so, please leave a comment!

Windows 7 DVD driver issue

After a windows update, my DVD drive suddenly went missing from explorer.
Device Manager shows a driver problem, but windows said the driver was fine.  Uninstall and reinstall had no effect.  Reboot had no effect.  I reseated the drive several times with no effect.

I downloaded the Optical Disk drive firmware update from IMB's site, but it couldn't even see the drive.

Then I came across this KB
http://support.microsoft.com/gp/cd_dvd_drive_problems

Apparently, the  UpperFilters and LowerFilters registry values can sometimes get screwed up when installing updates.  The program at the link above fixed the problem right away.



Did this help you?  If so, please leave a comment!

The new face of SSRS

The report builder in Visual Studio got a face lift in 2008.  I haven't run into anything significant that I can do in 2008 that I couldn't do in 2005, so I must assume this is yet another attempt to drive coders mad.

The first change I noticed was that the data tab was gone from the report.  Yes, they included a menu item called Report Data waaaaaay down at the bottom of the view menu in an entirely successful attempt to hide it.  Luckily, the GUI designers are powerless to totally foil our productivity, because the guys who actually code this stuff could care less about the interface. Enter the control key!  I soon discovered that CTRL + ALT + D pops that sucker right up.  It is now a dockable window with all the data objects in it and the ability to drag and drop to the report.  You have to create a datasource over here before you can create the datasets (what is the shared datasources in the solution explorer for again?) but once you figure out the redundancies, it is pretty smooth sailing.

You may ask yourself, "Self...why didn't they just add the functionality to the server explorer?"  Well, that would be too logicial.  However, if you answered yourself you might want to take a crack at GUI design :^))



Did this help you?  If so, please leave a comment!