USE OLTP

--use views to generate clean data
go
CREATE VIEW sales_view AS
SELECT date
       ,REPLACE(replace(trim(retailer_city),'Lodon','London'),'Londonn','London') as retailer_city
       ,order_method_type,urgent,retailer_type,product_code,sale_price,quantity_sold
FROM sales
go

--Multi-statement table-valued functions
go
CREATE FUNCTION sales_clean()
RETURNS @clean_data TABLE (
Date date
,Retailer_city char(120)
,Order_method_type char(120)
,Urgent char(5)
,Retailer_type char(120)
,Product_code char(5) 
,Sale_price money
,Quantity_sold int) as

BEGIN 
	INSERT INTO @clean_data
	SELECT * FROM sales

	UPDATE @clean_data
	SET retailer_city = trim(retailer_city)

	UPDATE @clean_data
	SET retailer_city = 'London'
	WHERE retailer_city in ('Lodon','Londonn')

	return 
END --the sales_clean() function
go

--Check the profiler
SELECT retailer_city,count(*) as count
FROM sales_clean()
GROUP BY retailer_city
ORDER BY 1,2 desc

--Now we have clean data
SELECT *
FROM sales_clean()
