select starting digitals followed by first word in mysql

I have lots of addresses in my database. example: 1199 State Route 218

I want to select the starting digitals followed by first word and ignore the rest of the address. so i want to retrieve address just by calling this: 1199 State

thanks for you help

Answer

If you are running MySQL 8.0, you can use regexp_substr():

regexp_substr(mycolumn, '^\d+\s+\w+')

Regexp breakdown:

^    beginning of the string
d+  a sequence of digits
s+  a sequence of spaces
w+  a sequence of word characters

Demo on DB Fiddle:

select regexp_substr('1199 State Route 218', '^\d+\s+\w+') v

| v          |
| :--------- |
| 1199 State |

---

On the other hand, if you are just looking to filter the table with the beginning of the adress, then like should be enough:

select address from listing where address like '1199 State %'

Note that the joker character (%) is preceeded by a space. This ensures that you are matching on the entire fist word rather than on part of it.