Wednesday, 5 September 2012

Update: HTC Desire HD turns itself off, hope for nice new phones

An update to my original post concerning random rebooting of the HTC Desire HD.

Even after an update was applied to the phone over the air, I found that it would regularly reboot itself even when I'd left it unattended.

The answer turned out to be that the SIM card was gradually loosening from its slot, which I think was the original root cause of the phone rebooting unexpectedly (I thought this was something to do with the Alarm Clock, but it seems that was more of a symptom than a cause).

I taped my SIM card down inside the phone so that it couldn't budge from its slot, and it has never rebooted itself again.

That's the good news. The bad news is that the phone is badly playing up these days. When I call other people it randomly puts them on hold and dials other people's numbers, and more often than not it will just terminate the call without warning.

So I think it's almost time to lay the ol' HTC Desire HD down to rest. It will be interesting to see what comes of the Apple, Nokia (not so much) and other phone launches. 

When they announce the iPhone 5 (seems like it will actually happen this time!), I hope that the new version of iOS includes proper 'Android-esque' widget functionality - for me this will make or break my decision to buy an iPhone. 

So come on Apple, make your OS properly content-centric - not just a bunch of brightly coloured icons.

Monday, 3 September 2012

SQL Server: Split delimited string to table variable

In one of my projects I had to split a string which held a list of names delimited by the forwardslash '/' character into a proper table structure, so that the values could be used in a relational format.

I tried a few things including putting a bunch of PATINDEX type commands together, until I found a solution which works (it may not be particularly scalable). This also trims whitespace from either side of the delimiter in case they aren't evenly spaced:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSplitDelimitedString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[fnSplitDelimitedString]

CREATE FUNCTION fnSplitDelimitedString (@list nvarchar(MAX),@delimiter VARCHAR(1))
   RETURNS @tbl TABLE (name varchar(255) NOT NULL) AS
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
      SELECT @nextpos = charindex(@delimiter , @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (name)
         VALUES (convert(varchar(255), ltrim(rtrim(substring(@list, @pos + 1, @valuelen)))))
      SELECT @pos = @nextpos

SELECT name FROM fnSplitDelimitedString('Gerry Smith /   Tom Martin / Michael Green/Rohil Sen/Dominic Davidson/Richard Wilson','/') as Split 

You can pass in your own delimiter (comma, semicolon etc), and the function returns a table in this format which is a whole lot more useful:

Gerry Smith
Tom Martin
Michael Green
Rohil Sen
Dominic Davidson
Richard Wilson

I found this solution on a blog post by Erland Sommarskog which has a lot more potential solutions. If the solution I posted doesn't work for you and you need something which will scale up, try reading his detailed write-up.

This solution was tried and tested on SQL Server 2005.