We all take things for granted. And one such thing are functions that we are familiar with. When we use a function for the first time, we use it in a certain way and it works. It works a second and third time, and it continues to work – we take it for granted. We expect it to work like it always has. Until one fine day – 10 years later, you find that it does not work the way you thought it would – but you still suspect that something else is wrong. You spend too much time trying to figure it out until you finally realize that it’s your dear old friend who’s gone wonky. It happens to all of us – or at least to those of us who don’t read documentation in its entirety.
I was just working on some T-SQL that required simple string manipulations. You know, the usual data from text file to staging table, pull out the field and throw in some functions to separate first and last names. I was using the LEN function to get the length of the string so that I can use it to pull out the last name. Something that goes like this:
1: RIGHT([EmployeeName], LEN([EmployeeName]) - CHARINDEX(' ', [EmployeeName]))
A simple employee name field, that separates first and last name with a space. This would work alright, except when there are trailing spaces. This I did not know (I’m ashamed to say). That was because I have always used LEN believing that it will return the length of the entire string, which it does but ignoring trailing spaces.
So how would you work around this? I can think up of two ways:
- Trim your string first:
1: RIGHT(RTRIM([EmployeeName]), LEN(RTRIM([EmployeeName])) - CHARINDEX(' ', RTRIM([EmployeeName])))
You would get longer codes, especially if it is more complex than my mere mortal example.
- or use the DATALENGTH function:
1: RIGHT([EmployeeName], DATALENGTH([EmployeeName]) - CHARINDEX(' ', [EmployeeName]))
DATALENGTH counts the number of bytes that your string contains (including that of your trailing spaces). But, you need to be forewarned that if it’s an Unicode expression that you are dealing with it would show double the length, since Unicode uses up 2 bytes per character.
So there you go, a post that was not supposed to be technical, but philosophical – Don’t take things for granted.