November 2015 – KC Database

Splitting Rows Out with SQL

Suppose you have the following table:

create table people_groups

(

group_name varchar2(32) constraint pk_people_groups primary key

, people varchar2(512) not null

);

insert into people_groups (group_name, people)

select 'Group 1', 'John,Elizabeth,Margaret' from dual union all

select 'Group 2', 'James' from dual union all

select 'Group 3', 'Samantha,Simon,Carla,Harold' from dual union all

select 'Group 4', 'Judy,Levi' from dual;

commit;

select *

from people_groups;

It looks like this:

But you want it to look like this:

How do you fix this using SQL? Continue Reading

select *

from my_table

where (color, num) in (('blue', 7),

('green', 15),

('white', 2),

('orange', 59))

Cool because it prevents you from writing a long series of “and”s and “or”s

select *

from my_table

where (color = 'blue' and num = 7)

or (color = 'green' and num = 15)

or (color = 'white' and num = 2)

or (color = 'orange' and num = 59)

Please–stop substringing dates. There are much easier, more efficient, and more robust ways of getting what you want. Continue Reading