Sql like expresion result too new column

Question

Hi everybody I need done help with this issue.

Example myDb tabele name people

ID          name        Sex
3   Adam Smith      M
5   Sonia Kolan     F
3   Donald Smith    M

Select id, name from people where name LIKE ‚%Smith%’;

Result

Id   Name 
3   Adam Smith
3   Donald smith

My question is how to transform my result of col name to this view

Id   name  
3    Smith 
3    Smith

I want too see in col name Expresion from like statement

I think this is a duplicated question of this: https://stackoverflow.com/q/4263272/3317006 — MAZux, Oct 28 '20 at 09:55
What name should be shown in case `John Smithson`? Is it should to be `Smith` or `Smithson` — Slava Rozhnev, Oct 28 '20 at 11:36

score 1 · Answer 1 · answered Oct 28 '20 at 09:51

1

I want too see in col name Expresion from like statement

Then just put that literal string in the select clause:

select id, 'Smith' name
from people 
where name like '%Smith%'

answered Oct 28 '20 at 09:51

GMB

forpas · Answer 2 · 2020-10-28T09:57:38.930

0

You can join to the table a query that returns only 'Smith':

select p.id, t.name 
from people p
inner join (select 'Smith' name) t
on p.name LIKE concat('%', t.name, '%');

See the demo.

Results:
> id | name 
> -: | :----
>  3 | Smith
>  3 | Smith

edited Oct 28 '20 at 09:57

answered Oct 28 '20 at 09:52

forpas

2 Answers2