Blog ala Vidar

In .NET, if you have a string it’s:
VB: Dim vMyString As String
C#: string vMyString;

In SQL Server you’ve got more than one choice. varchar, nvarchar (with or without MAX), char, nchar, text and ntext. That’s why SQL Server is better than .NET. No, just kidding. When you’ve got a String in .NET, what’s the equivalent? nvarchar. If you’re trying to put varchar in .NET, you’ll end up with an nvarchar (since the text is changed to Unicode).

SQL is built to store large amounts of data and retrieving it faster than lightning. It’s not built to handle strings, but sometimes you need to do it on the SQL Server. That’s when functions like SUBSTRING, LEFT, RIGHT, REVERSE and PARSENAME are useful! The last one, PARSENAME, is a function that’s saved me A LOT of time. The purpose of this function is to retrieve table names, schema names etc from a string. For example a string like “Server.Database.Schema.Table”. If you put PARSENAME and set the second parameter to 1, you’ll always get the Table. 2 will always give you Schema etc. Now, how can we use this other than retrieving object-names?

DECLARE @Name AS NVARCHAR(100)

SET @Name = 'Nordnes, Vidar Stangeland'
SET @Name = REPLACE(@Name, ',', '.')

SELECT
  PARSENAME(@Name, 1) AS FirstName,
  PARSENAME(@Name, 2) AS LastName

The @Name variable contains a name where you’ve got “LastName, FirstName (MiddleName)”. Replacing comma with a period enables you to use PARSENAME. The cool thing about this is that it returns NULL if it doesn’t have any values. If you didn’t use PARSENAME, you’d have to do something like:

DECLARE @Name AS NVARCHAR(100)

SET @Name = 'Nordnes, Vidar Stangeland'

SELECT
  CASE WHEN CHARINDEX(',', @Name) > 0
	THEN SUBSTRING(@Name,
			CHARINDEX(',', @Name) + 2,
			LEN(RIGHT(REVERSE(@Name),
				CHARINDEX(',',
					REVERSE(@Name)))))
	ELSE @Name
  END AS FirstName,
  CASE WHEN CHARINDEX(',', @Name) > 0
	THEN LEFT(@Name, CHARINDEX(',', @Name) - 1)
	ELSE @Name
  END AS LastName

WAY more time consuming, for doing something that should be pretty easy. REVERSE is also a great function to know about. As is REPLICATE, STUFF, ISNULL (or COALESCE), LTRIM and RTRIM.

While I’m at it, “asdf “ (with trailing space) isn’t always the same as “asdf” (without trailing space). Just take a look at this example:

SELECT '1' WHERE 'asdf' = ' asdf'
UNION ALL
SELECT '2' WHERE 'asdf' = 'asdf '
UNION ALL
SELECT '3' WHERE 'asdf ' = ' asdf'
UNION ALL
SELECT '4' WHERE 'asdf ' = 'asdf'
UNION ALL
SELECT '5' WHERE 'asdf' LIKE ' asdf'
UNION ALL
SELECT '6' WHERE 'asdf' LIKE 'asdf '
UNION ALL
SELECT '7' WHERE 'asdf ' LIKE ' asdf'
UNION ALL
SELECT '8' WHERE 'asdf ' LIKE 'asdf'

Before you run the code, think about what you THINK will be returned. 1-4 is the same as 5-8 except the = vs LIKE. My first guess was 0 records. My second guess was 2 and 6. None of my guesses was correct though.

Remember also that a nvarchar(MAX) field can contain very much data. Returning this to the client could be very slow, especially if you include this field in other fields (for instance one field returning HTML-data, and one without the HTML-formatting), or as we often see here in Omega, a SearchColumn which is used by our framework for filtering. This field normally consist of all the (n)varchar-fields in the table.