How to sort alphabetically in SQL excluding extra words?

Question

MySQL

Let's say there is an Address table with name fields.
Such records:
Belova
st Aleksey
st Fadeev st
Due to the fact that there is a 'st' at the beginning, the usual alphabetical sorting ORDER BY name ASC does not work.
How to solve the problem?

Answer 1

If you need to sort so that "Belova street" and "Belova passage" are nearby, then ...
option 1: call them "Belova street" and "Belova passage".
option 2: store in separate fields "street" + "Belova" and "passage" + "Belova". And sort by second

Answer 2

It's strange that it doesn't work, because sorting alphabetically means sorting by 4 characters if the first three are the same...

Answer 3

Bring to a general view the format of the street.
To be st. Academician and there was no "Academician's street", "Academician's street" and so on. In pgsql, you can create separate functions for this, using a whole bunch of built-in functions for working with text. I don’t know how to do this in mysql, but I would very quickly implement it for myself in python using formatting and regular expressions. After that, you can sort. If there are not only streets, separate the address into two fields. 1st field - type (street, highway, lane, etc.), 2nd field - name (Akademika Yangelya, Electrodnaya, Parkovy). Thus, you can sort this table as you like.

Answer 4

Due to the fact that there is a 'st' at the beginning, the usual alphabetical sorting ORDER BY name ASC does not work. How to solve the problem?

In the forehead: remove from the title "excluding unnecessary words" and sort. Either add or calculate a custom sort field.