T-SQL: How to Order by Surname in Full Name Column?

Sometimes you need to do ordering by in a full name column. If you want to order by first name, that’s cool, but when you need to order by last name, you need to find the surname for each field.

Let’s take a look at this data:

[Full Name]
Albert Einstein
Leonardo da Vinci
Nicolas Tesla

These values are ordered by first name so there’s no problem with that. But if we wanted to order by last name(which is not a particular column in this table), we had to find a way like splitting these fields or something else.

Anyway, there was a good line of code in StackOverflow (see question&answer here) written by user Recep.

SELECT [Full Name], REVERSE(SUBSTRING(REVERSE([Full Name]), 0, CHARINDEX(' ', REVERSE([Full Name])))) AS SurnameAfterReversing
--We need to select our ORDER BY expression as a new column otherwise it will warn that it can't order by a column which is not exist in query result.

It reverses the whole name here, then gets text until the first space (‘ ‘) character, then reverses the new text back. That way, we actually get the last name so you can do anything you want with this column.

And here is the data as a result of this query:

[Full Name] SurnameAfterReversing
—————————- —————————-
Albert Einstein Einstein
Nicolas Tesla Tesla
Leonardo da Vinci Vinci
1 Comment

Generate Random Number and String in MS SQL Server

Sometimes you might wanna generate random number or string in MS SQL. For example, I do it for temporary user passwords.

Here’s how you generate a random number. X is the max value, the code is generating between 0 and (X-1).


Be aware that max. generated number is X-1 because it’s generating X numbers starting from 0(zero).

If you generate 5 numbers, your range will be: {0, 1, 2, 3, 4}.


Another code is generating random string(VARCHAR). Here it is:

SELECT SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 9)

Attention to 0, 9. It’s substring range, here I’m generating a string value whose length is 10 characters.

1 Comment