Making Letters Do What Want

In our databases, we’ll find all kinds of string data. Sometimes, you’ll find a reason to work with alphanumeric data individually in different ways. Let’s go through an example that I worked with recently that is not quite the Norm.

The business unit I work with wanted to categorize a specific group of people into 10 different categories. They wanted them to be known as “A”, “B”, “C”, “D”, etc… all the way through “J.”

Being a good developer, I didn’t want to have to hard-code into my SQL queries the following:

SELECT 
	'A' AS PersonType,
	ID AS PersonID,
	FirstName,
	LastName
FROM dbo.Person1
WHERE LastName LIKE 'A%'

SELECT 
	'B' AS PersonType,
	ID AS PersonID,
	FirstName,
	LastName
FROM dbo.Person1
WHERE LastName LIKE 'B%'
-- etc... etc... etc...

Besides the fact that it takes up a lot of screen real estate, if there are any changes to the logic used in the WHERE clause, I’ll have to do lots of copy/paste work – which I’m just not a huge fan of if I can get out of it.

The first thing I need to do is to figure out what the ASCII code for “A” is. (Yes, that is a special way that the computer encodes the letter “A” so that it know what an “A” is.)

I use the following code to figure that part out.

SELECT ASCII('A')

Now that I know that “A” is equivalent to 65 – note that “a” is not 65 since it is lower-case – I can begin to work to get all the data I need.

Thankfully, in our case, we just want to do letters “A” through “J”, which ends up being ASCII codes 65 – 74.

Thankfully, there is a function in SQL – CHAR – that will convert the numbers 65 – 74 back to the letter that we wish to display.

To finalize the process, we can use a WHILE loop to loop through all of the letters we need and be done with this process.

Our final bit of gloriousness is shown in the code below:

DECLARE @I INT = 65		-- A

WHILE @I <= 74	-- J
BEGIN
	SELECT CHAR(@I) AS PersonType,
		ID AS PersonID,
		FirstName,
		LastName
	FROM dbo.Person1
	WHERE LastName LIKE CHAR(@I) + '%'

	SET @I = @I + 1
END -- End of WHILE Loop

This code does exactly like the code above, but with the added benefits of being smallish and easily maintained.

Also, now if our business partners want to change the letters to Q thru Z, we just have to change the numbers we loop through and we’re done!

Until next time my friends, when we’re going to do some more fun string manipulation!