SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (`name` TEXT, `domain` TEXT);
✓
INSERT INTO table1 (`name`, `domain`) VALUES
  ('Apple', 'gmail'),
  ('.Apple2', 'yahoo'),
  ('_Apple3', 'xyz'),
  ('Apple4', 'zyx'),
  ('Ball', 'abc'),
  ('.Ball2', 'efg'),
  ('_Ball3', 'gmail'),
  ('Ball4', 'yahoo'),
  ('Bear', 'xyz'),
  ('Cat', 'abc');
✓
SELECT * FROM table1
ORDER BY domain = 'yahoo' DESC,
         domain = 'gmail' DESC,
         name GLOB '[A-Za-z]*' DESC,
         LTRIM(LTRIM(name, '.'), '_');


name
domain


Ball4
yahoo

.Apple2
yahoo

Apple
gmail

_Ball3
gmail

Apple4
zyx

Ball
abc

Bear
xyz

Cat
abc

_Apple3
xyz

.Ball2
efg

name	domain
Ball4	yahoo
.Apple2	yahoo
Apple	gmail
_Ball3	gmail
Apple4	zyx
Ball	abc
Bear	xyz
Cat	abc
_Apple3	xyz
.Ball2	efg