CODEWITHSUNDEEP
sqlsql-serverms-access
Beckie
Beckie

Reputation: 73

Sort Everything After A Specific Character in SQL

I am needing to sort a field on everything after a space usig SQL. In the example below, I would like it to sort (ascending) beginning with the last name.

USA-J. Doe
USA-M. Mouse
USA-A. Mouse
USA-D. Duck
USA-P. Panther
USA-T. Bird

I need it to sort the entire string, but on the last name. If there are two last names that are identical, I would like for it to take the initial of the first name into account. The result would be:

USA-T. Bird
USA-J. Doe
USA-D. Duck
USA-A. Mouse
USA-M. Mouse
USA-P. Panther

I will need to use this code in both SQL Server and MS Access.

I hope that someone can fully answer this question. For whatever reason, someone has scored me a -1 on this question. I cannot figure out why. I have been as specific as I know to be and I wasn't able to find an answer to the final piece--sorting by first letter if the last name is the same.

Thank you guys for responding. The information helped. I had to add brackets around "name" because the name of the field was similar to the name of the actual table.

Upvotes: 1

Views: 2249

Answers (2)

masum7
masum7

Reputation: 832

For sorting by lasname, firstname you need to use like this in SQL server:

order by substring(name, charindex(' ', name) + 1, len(name))), substring(name, charindex('-', name) + 1, 1))

In Access

order by mid(name, instr(name, ' ') + 1), order by mid(name, instr(name, '-') + 1, 1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

This depends on the database. The following is how you might do this in SQL Server:

order by substring(name, charindex(' ', name) + 1, len(name)))

Similar logic works in other databases but the functions are different.

For instance, in Oracle:

order by substr(name, instr(name, ' ') + 1)

And, in MySQL, you could use similar logic, but this is simpler:

order by substring_index(name, ' ', -1)

And in MS Access:

order by mid(name, instr(name, ' ') + 1)

Upvotes: 4

Related Questions