How to use REPLACE() within NTEXT columns in SQL Server

SQL has an incredibly useful function, REPLACE(), which replaces all occurrences of a specified string with another string, returning a new string. It works great with all forms of NCHAR and NVARCHAR fields. It does not, however, work with NTEXT fields.

Fear not — there’s an easy workaround, thanks to type-casting and SQL 2005’s NVARCHAR(max) datatype. Here’s the process in an nutshell.

  1. Cast the NTEXT field to the NVARCHAR(max) datatype using the CAST function.
  2. Perform your REPLACE on the output of #1.
  3. Cast the output of #2 back to NTEXT. (Not really required, but it does get us back to where we started.

A simple SQL query illustrates this.

select cast(replace(cast(myntext as nvarchar(max)),'find','replace') as ntext)
from myntexttable

If you’re using SQL 2000, you’re out of luck, as NVARCHAR(max) first appeared in SQL 2005. However, if your NTEXT field is less than 8000 characters, you can cast it to VARCHAR(8000) — the largest possible VARCHAR size — to accomplish the same.

[Note #1: This solution below will also work with TEXT fields. Simply replace NTEXT with TEXT, and NVARCHAR with VARCHAR.]

[Note #2: NTEXT fields are depreciated in SQL 2005 in favor of NVARCHAR(max), so avoid using NTEXT and you’ll avoid this problem altogether in the future.]

Operator overloading your domain model with interfaces and base classes

One of the challenges in rewriting my online baseball game is dealing with enormous amounts of data that needs to be stored as aggregates, and coming up with a domain model and data mapping pattern that works. In this blog post, I’ll outline how I addressed some of those issues.

The Data Model

Baseball is very much a statistics-oriented game. Consider fielding statistics: putouts (PO), assists (A), errors (E) and others. These stats need to be stored:

  • Per game, for each player who played in the game, for each position he played (key fields: game, player, position)
  • Per season, for each player, for each team he played for, for each position he played (key fields: season, player, team, position)
  • Career, for each player, for each position he played (key fields: player, position)

On the database side, that results in three tables: GameFieldingStats, SeasonFieldingStats, and CareerFieldingStats. Each has the same set of fields to store the statistics (PO, A, and E); the differences are in the key fields for each, as outlined in the diagram below. (Note: For the remainder of this post, I’ll include only the first two of those tables to keep things short.)

Continue reading

Wilson.ORMapper gets a few small updates

I’ve finally gotten around to updating the public open source version of Wilson.ORMapper to match the release formerly known as Subscriber Preview 4.2.2.1. Revision 10 in the SVN repository matches the original 4.2.2.1, but includes the non-restrictive open source license.

I then went one step further — adding a patch which fixes a known bug in WORM. This patch also fixes unit test failures in WilsonORWrapper, which has also been updated to reference version 4.2.2.2 of WORM — the first truly official public release that has some code not provided directly from the original creator, Paul Wilson.

Check out the Google Code project at http://code.google.com/p/wilsonormapper/, or discuss on the WilsonORMapper Google group.

Looking for a good developer — are you the one?

For the past month I’ve tried to find a few people to work with me on some projects — between 10 and 25 hours a week, maybe more, for up to three months, maybe much longer. It’s very flexible work — you tell me how much time you can dedicate and I’ll work with you. I don’t care where you work or where you live, so long as you are willing to communicate with me effectively.

So far, two people have been absolute failures, one is still a work in progress, and one has worked out perfectly. Amazingly, the guy who worked out lives in Spain, and we never spoke a word to each other — all our dialogue is via IM and e-mail.

The right candidate should:

  • Be thoroughly familiar with ASP.Net, C#, and the .Net Framework
  • Know the right way to develop web sites (XHTML, CSS, etc.)
  • Understand what usability and accessibility mean, and be able to design user interfaces that are simple, effective, and user-friendly
  • Be able to take business requirements and make them into a useful product
  • Enjoy using or want to use technologies outside the Microsoft mainstream (MonoRail, O/R mappers, JS libraries, Subversion, etc.)
  • Have a passion for technology
  • Be available 10-25 hours (or more) a week
  • Enjoy a flexible work schedule and casual work environment

If you can fit part of that bill, please let me know. All work would be paid as contract labor (either to your company or to you as an individual; the latter would require you to receive a US 1099 income form at the end of the year), and there may be good room to grow.

Please email your desired compensation (hourly rate unless you prefer project rate) and resume/credentials to bdemarzo@gmail.com.

A Windows user’s first four days with a Mac

On Friday, I received my iMac, bringing me back to the world of the Mac OS for the first time in about eight years. The iMac is not my main rig — I still use a “Wintel” PC running Windows XP for work — it’s a replacement for my home computer (though I eventually hope to expand its use beyond pictures, movies, and World of Warcraft).

So, what’s the initial reaction from a guy who was very entrenched into Windows, but has a solid Mac history?

  • What’s up with the mouse movement? One of the first things I do on any computer I use is turn the mouse speed up all the way. I want the slightest flick of my wrist to shoot the cursor across the screen. Doing this on a Mac made it, well, not as zippy as I’d prefer. Apparently, plenty of people agree, and the fix to the problem is to use some freeware hacks or shareware software (SteerMouse did the job for me).
  • I miss my keyboard shortcuts. Yes, you can do a lot with the Mac keyboard, but I do almost everything with the PC keyboard. I’ve since learned you can press Control-F2 to open the Mac menu for keyboard navigation, but I miss the ability to TAB between fields in web browsers (there is a fix) and the underlined letters that show which keys you can press to activate menu options (somewhat of a workaround).
  • Installing software on a Mac is glorious. I can’t believe how easy they make it. No surprises and no issues.
  • I plugged in my USB devices and they worked immediately — my external USB drive, my digital camcorder, my digital camera, and my photo printer. That never happened on a PC.
  • iMovie is everything that Windows Movie Maker is not.
  • FolderShare works as well on a Mac as it does on a PC — and it made transferring dozens of gigabytes of pictures and movies disturbingly simple.
  • I had to manually turn on the right-click feature on my single-button MightyMouse. That should be on by default.
  • World of Warcraft stopped working after I adjusted some parental controls and firewall settings — apparently an unintended side effect acknowledged by Blizzard — and required a reinstall. Fortunately, reinstall only took me about 30 minutes, about the time it takes to queue up and play one battleground instance.

I’m enjoying the Mac experience so far. It’s definitely something to get used to. My fingers still stumble on the Mac keyboard a bit, and there are some things I miss from Windows-land.