SQL Regex Search for Full name
===

It's probably really simple.
So, I have this table, user
That has a field 'full_name'

Say I have entries like 

    "John Doe"
    "Jane Doe"
    "Colby Smith Jr"
    "Apple Smith"

I need a regex search that will fit these conditions:

    query = John => return 'John Doe'
    query = Doe => return 'John Doe', 'Jane Doe'
    query = Apple => return 'Apple Smith'
    query = Smith => return 'Colby Smith Jr', 'Apply Smith'
    query = Apple Smith => return 'Apple Smith'
    query = Smith Jr => return 'Colby Smith Jr'

So basically, I need a regex function that will search 
query* (* being wildcard?)

OR
(first char after a space in my result strings)query

Does that make sense?  
Basically, I'm trying to mimic the functionality in instagram's search.  Check that out if you can. 