What Expression function to use?

[pg002a]

HI

I messing around how to create expression filter for address search. I want to create address search for data which come from  database column where are whole address like "Tallinn st 100" st means street.  I dont want to input "st" in search widget, only street and house number like inpu only "Tallinn 100". Does I have to make new "house nr" and "street name"columns in database or it is possible to do it with Studio, like converting text to be without some character(my case without "st"). I read Studio help for expressions but didnt figured out what kind of function to use.

[tjmapper]

If your address had the house number first it would make things less complicated because they could just search for "100 Tree" and get back "100 Tree St, 100 Tree Rd, 100 Tree Dr, etc" if you used the expression:

"ADDRESS LIKE '%$USER_VARIABLE%'"

but you can also have the user enter the % into the search and it will do the same. So they could enter "Tallinn % 100" and it should work.

[pg002a]

Adding % character before and after user variable I already know, I just thought that there is other way to do it using some conversion function. I want to create user friendly search function so user dont need to use any %.