Replace Data in SQL and IF Statements

 String Manipulation

1. REPLACE: Replace specified characters with others.
2. SUBSTRING: Extract specific characters.
3. CONCAT: Combine multiple strings for formatting.

Conditional Statements

1. IF: if(condition, value when condition is true, value when condition is false)
2. CASE WHEN END:

 

case when condition1 then value(expression)1
     when condition2 then value(expression)2
     else value(expression)3
end

 

 

 

While I was studying SQL, I wondered if it is possible to revise the data, and I would like to write down notes about what I studied for this.

 

Replace

select restaurant_name "Before",
       replace(restaurant_name, 'Blue', 'Pink') "After"
from food_orders
where restaurant_name like '%Blue Ribbon%'

 

 

Result

 

 

 

While using SQL, I wondered if it was possible to use a newly created column in another query. After trying various approaches, it seems that the newly created column couldn't be used directly. However, I found that I could obtain the result by using the formula which I used for the newly created column.

 

I wanted to use the column name '시도', but since it was newly created, I wasn't able to use it. Therefore, instead of using the newly created column name, I brought the formula that I used to create the new column again.

 

+ It is possible to use a newly created column by using Subquery

SELECT 
    addr AS "원래 주소",
    SUBSTRING(addr, 1, 2) AS "시도",
    CASE
        WHEN SUBSTRING(addr, 1, 2) = '서울' THEN '서울'
        ELSE '기타'
    END AS "시도_포함여부"
FROM 
    food_orders
WHERE 
    addr LIKE '%서울특별시%'

 

 

 

Concat(enate) - 

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

 

 

result

 

 

Group by practice

 

When you use 'group by', you can put 1, 2 instead of putting all the sentences from the 'select' line

 

select cuisine_type,
	   substr(addr, 1, 2) 'location',
	   addr,
	   price,
	   AVG(price) "average price"
from food_orders fo
where addr like '%서울%'
group by 2, 1

 

 

IF statement

 

I have used an IF statement to obtain the result. I was wondering if I can integrate another formula into the IF statement, and it worked well.

 

select email, substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "email_domain",
       count(customer_id) "count_customer",
       avg(age) "average_age"
from customers
group by 1

›

 

 

 

 

Case

 

Compared to the IF function, the CASE function seems more convenient and versatile. I guess i'm going to use the CASE function more frequently than the IF function in the future.

 

select restaurant_name,
       addr,
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substring(addr, 1, 2) end "New_address"
from food_orders

 

 

result

 

 

Practice 1

 

select restaurant_name,
       price/quantity "Value",
       cuisine_type,
       order_id,
       case when (price/quantity <5000) and cuisine_type='Korean' then 'Korean1'
            when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then 'Korean2'
            when (price/quantity > 15000) and cuisine_type='Korean' then 'Korean3'
            when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then 'Asia1'
            when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then 'Asia2'
            when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then 'Asia3'
            when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then 'else1'
            when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then 'else2'
            when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then 'else3' end "Restaurant_group"
from food_orders

 

 

Result

 

 

Practice 2

 

SELECT case when day_of_the_week='weekday' then 3000*if(quantity>3, 1.2, 1)
			when day_of_the_week='weekend' then 3500*if(quantity>3, 1.2, 1)
			end "Delivery_fee",
			restaurant_name,
			order_id,
			day_of_the_week,
			quantity 
FROM food_orders fo

Result

 

 

Error Note

 

When I tried to perform calculations with both characters and numbers, I encountered an error.

 

->  I used SQL queries at work, but when I used functions like avg, substring, I got an error message saying 'data type', and the queries wouldn't execute. 

• It is possible to be happened in other SQL syntax, operations may fail if the data types are different.
• Looking at the example from 1), even though 'rating' contains numbers, it's stored as a character type.
  (Please check the 'ABC' or '123' next to the column names in the output. 'ABC' indicates it's stored as a character.)