Order sql result by first occurence of string

Question

Given the following sql statement:

select * from mytable where mycolumn like '%xyz%'

and the following result

aaxyz
bbbxyz
xyzcc
dxyzxd
eeeeexyz

How do I demand the sql statement to order the result in the rank of the first occurence of the demanded string, i.e. xyzcc first and eeeeexyz last?

Answer 1

With the function position() which returns the index of the first occurrence of a substring inside a string:

select * from mytable 
where mycolumn like '%xyz%'
order by position('xyz' in mycolumn)

See the demo.
Results:

| mycolumn |
| -------- |
| xyzcc    |
| dxyzxd   |
| aaxyz    |
| bbbxyz   |
| eeeeexyz |