sql
random record from a aggregation
MAX(scn.guid) KEEP (DENSE_RANK FIRST ORDER BY DBMS_RANDOM.VALUE) AS random_sample
insert
insert into TABEL (a_id,t_id,dtm) values(123,456, systimestamp);
date
where dt>to_date('01-07-2023','DD-MM-YYYY')
record met wijzigingsdatum vandaag
where u.dt>=trunc(sysdate)
case / if
CASE WHEN a IS NOT NULL THEN 'ja' ELSE 'nee' END AS "gescand"
nodig als je meerdere langere teksten aan elkaar wilt plakken met group_concat. staat standaard op 1024
$db->prepare("SET GLOBAL group_concat_max_len=100000;")->execute();
create slug from title
CONCAT('story/',LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tv.field_cast_video_title, ' ', '-'), ',', ''), '.', ''), ':', ''), '/', '-'),'''',''),'---','-'),'--','-')))) "url_alias",
via 'CONNECT BY PRIOR' alle 'dieper liggende record' vinden op basis van de ID van een bovenliggend record
SELECT * FROM appels
START WITH ID = 1305602
CONNECT BY PRIOR ID = ParentID;
order by in a group_concat (mysql)
select
node.nid "ID",
#...
group_concat(rel.field_location_target_id order by rel.delta) "locaties"
from node
#...
left join field_revision_field_location rel on rel.entity_id = node.nid
where node.type='series'
group by
node.nid,
#.....
;
recursive title lookup for titles (TRAVERSING!)
SELECT LISTAGG(a.id, '...')
FROM apples a
CONNECT BY PRIOR parent_id = id
START WITH id=123123
ORDER BY level DESC
or with a subselect to get apple titles from another table:
SELECT LISTAGG((SELECT title FROM apple_descriptions b WHERE b.id=a.id),' ... ')
FROM apples a
CONNECT BY PRIOR parent_id = id
START WITH id=123123
ORDER BY level DESC
or with a join to retrieve titles from the titles table:
select listagg(t.title, '...')
from apples a
join apple_titles t on t.id=a.id
connect by prior a.parent_id = a.id
start with a.id=724423
order by level desc
order by (within group)
SELECT LISTAGG(b.title, ' ... ') WITHIN GROUP (ORDER BY level DESC) "context"
FROM apples a
JOIN apple_titles b ON b.id=a.id
CONNECT BY PRIOR a.parent_id = a.id
START WITH a.id=724423
oracle version
select * from v$version;
changes on a certain day
where aew.dt >= DATE '2023-01-05' and aew.dt < DATE '2023-01-06'
ignore &'s in sql values
set define off
date field condition
select * from TABLE where dt >= DATE '2022-12-22'
rownum (oracle)
where ROWNUM<5000
select case select
case when MY_FUNC(persoon.id, 'ROL')='Bruid'
then concat(' ',persoon.beschrijving)
else persoon.beschrijving end "Persoon"
for every record replace part of the value
UPDATE adressen
SET straat = REPLACE(straat, 'https://data.kkg.kadaster.nl/id/openbareRuimte/', '')
regexp_like
regexp_like(beschrijving, '^1221-1|^1221-2|^1221-3|^1221|^1232|^1394|^1395|^1396|^1397|^463|^481')
regex_substr
REGEXP_SUBSTR(besch.beschrijving, '(\()(\d*)\)')
regexp_replace
regexp_replace(besch.beschrijving, 'xxx', '')
regexp_replace with substr / groups
regexp_replace(REGEXP_SUBSTR(besch.beschrijving, '(\()(\d*)\)'), '\(|\)','') "AantalFotos"