giving an alias name to 'like' operation in mysql

select userName as UN like '%Bo%' or userName like '%nic%' from Names 

the output table disp开发者_JS百科lay as

UN
Bob
Bobby
Bony
nick
nikitha

here I want to give a common name for Bob,Bobby and Bony and another common name for nick and nikitha.

Can I do it?

I tried like this.

select userName as UN like '%Bo%' as male or userName like '%nic%' as female from Names

Bt error in output.How can I do this?

---

SELECT (CASE WHEN userName LIKE '%bo%' THEN 'Male'
             WHEN userName LIKE '%nic%' THEN 'Female'
             ELSE 'Unknown'
       END) AS sex,
       userName AS UN
FROM name;

I think that's what you want. You'll get a separate column called sex which indicates whether the name is male or female (or neither) and then your UN column with the actual name.

You can add a filter in the WHERE clause if you want to search for ONLY names matching one of those two patterns. My sense is that you really just wanted to see which names were male and which were female out of all the names, so I left that part off.

---

I read you question like this: you want to select the userName values that are like %Bo% or %nic% and you want to know which of the two patterns was matched.

If I'm right, then you want a CASE statement to generate your "which one matched" flag value, something like this:

select userName as UN,
       case when userName like '%Bo%' then 'Bo' else 'nic' end as which_one
from Names
where userName like '%Bo%'
   or userName like '%nic%'

Then you look at which_one to see which one matched, if %Bo% matched then which_one will be 'Bo', otherwise it will be 'nic' and %nic% matched.

---

    SELECT  CASE WHEN userName LIKE '%Bo%' THEN 'Common name for Bo' -- replace string inside quotes with the common name
                  WHEN userName like '%nic%' THEN 'Common name for nic' -- replace string inside quotes with the common name
                  ELSE userName -- In any other cases return the username
   END AS UN
    FROM Names