create table dupes (id integer, name varchar(10));
insert into dupes values (1, 'NAPOLEON');
insert into dupes values (2, 'DYNAMITE');
insert into dupes values (3, 'DYNAMITE');
insert into dupes values (4, 'SHE SELLS');
insert into dupes values (5, 'SEA SHELLS');
insert into dupes values (6, 'SEA SHELLS');
insert into dupes values (7, 'SEA SHELLS');

	create table dept_accidents
	( deptno         integer,
	  accident_name  varchar(20) );

	insert into dept_accidents values (10,'BROKEN FOOT');
	insert into dept_accidents values (10,'FLESH WOUND');
	insert into dept_accidents values (20,'FIRE');
	insert into dept_accidents values (20,'FIRE');
	insert into dept_accidents values (20,'FLOOD');
	insert into dept_accidents values (30,'BRUISED GLUTE');

Chapter 6. Working with Strings

select substr(e.ename,iter.pos,1) as C 
from 
   (select ename from emp where ename = 'KING') e 
   INNER JOIN
   (select id as pos from t10) iter
where iter.pos <= length(e.ename)

create view V2 as
	select ename as data
	  from emp
	 where deptno=10
	 union all
	select concat(ename,', $',sal,'.00') as data
	  from emp
	 where deptno=20
	 union all
	select concat(ename,deptno) as data
	  from emp
	 where deptno=30

select name, case
		when cnt=2 then concat_ws('.',substr(substring_index(name,' ',1),1,1),
                        substr(substring_index(name,' ',-2),1,1),
                        substr(substring_index(name,' ',-1),1,1),'') 
        else concat_ws('.',
	         substr(substring_index(name,' ',1),1,1),
             substr(substring_index(name,' ',-1),1,1),'')
       end initials
from (select name,length(name)-length(replace(name,' ','')) as cnt
      from (select replace('Stewie Griffin','.','') as name) y
	  ) x;


select name, case
		when cnt=2 then concat_ws('.',substr(substring_index(name,' ',1),1,1),
                        substr(substring_index(name,' ',-2),1,1),
                        substr(substring_index(name,' ',-1),1,1),'') 
        else concat_ws('.',
	         substr(substring_index(name,' ',1),1,1),
             substr(substring_index(name,' ',-1),1,1),'')
       end initials
from (select name,length(name)-length(replace(name,' ','')) as cnt
      from (select replace('Stewie E. Griffin','.','') as name) y
	  ) x;

select deptno,
     group_concat(ename order by empno separator ',') as emps
 from emp
 group by deptno

select empno, ename, sal, deptno	
from emp
where empno in (

select substring_index(
       substring_index(list.vals,',',iter.pos),',',-1) empno
FROM
(select id pos from t10) as iter,
(select '7654,7698,7782,7788' as vals) list
where iter.pos <= (length(list.vals)-length(replace(list.vals,',','')))+1

)

select ename, group_concat(c order by c separator '')
from (
   select ename, substr(a.ename,iter.pos,1) c
   from emp a
   INNER JOIN
   ( select id pos from t10 ) iter
   where iter.pos <= length(a.ename) ) x
group by ename

create view V3 as
	select concat(
	         substr(ename,1,2),
	         replace(cast(deptno as char(4)),' ',''),
	         substr(ename,3,2)
	       ) as mixed
	  from emp
	 where deptno = 10
	 union all
	select replace(cast(empno as char(4)), ' ', '')
	  from emp where deptno = 20
	 union all
	select ename from emp where deptno = 30

SELECT cast(group_concat(c order by pos separator '') as unsigned) as MIXED1
 FROM
(select v3.mixed, iter.pos, substr(v3.mixed,iter.pos,1) as c
from V3
 INNER JOIN
( select id pos from t10 ) iter 
where iter.pos <= length(v3.mixed) 
and ascii(substr(v3.mixed,iter.pos,1)) between 48 and 57) y
GROUP BY mixed
ORDER BY mixed1

create view V4 as
	select 'mo,larry,curly' as name
	 union all
	select 'tina,gina,jaunita,regina,leena' as name

SELECT name 
FROM
(select iter.pos, substring_index(substring_index(src.name,',',iter.pos),',',-1) name
from V4 src
INNER JOIN
(select id pos from t10) iter
where iter.pos <=
	 length(src.name)-length(replace(src.name,',',''))
) x
WHERE pos=2

select substring_index(substring_index(y.ip,'.',1),'.',-1) a,
       substring_index(substring_index(y.ip,'.',2),'.',-1) b,
       substring_index(substring_index(y.ip,'.',3),'.',-1) c,
       substring_index(substring_index(y.ip,'.',4),'.',-1) d
FROM
(select '92.111.0.2' as ip) y

Chapter 7. Working with Numbers