23 maj 2016

Przykład użycia funkcji GROUP_CONCAT

select GROUP_CONCAT(kto order by kto ASC SEPARATOR ', ') from raport_ludzie_spotkania

Złączenie rekordów wątku do jednego stringa, wycinanie 80 znaków stringa:

select t.ticket_id, t.created,
SUBSTRING(last_notes_title, 1, (80 + LOCATE(' ', last_notes_title, 80))) as last_notes_title,
SUBSTRING(last_notes_body, 1, (80 + LOCATE(' ', last_notes_body, 80))) as last_notes_body
from (
SELECT ticket_id, created,
GROUP_CONCAT(title ORDER BY created DESC SEPARATOR '|') AS last_notes_title,
GROUP_CONCAT(body ORDER BY created DESC SEPARATOR '|') AS last_notes_body
FROM ost_ticket_thread WHERE ticket_id IN (1,2,3,4,5) GROUP BY ticket_id
) as t
3 wrz 2008

Wykorzystanie LOCATE i REGEXP

SELECT LOCATE( ' ', address ) pos, address, 
SUBSTRING_INDEX(address, ' ', -1), address REGEXP '[:digit:]' 
FROM hotele

SELECT LOCATE( ' ', address ) pos, address, 
SUBSTRING_INDEX( address, ' ', -1 ) , address REGEXP '[[:digit:]]' 
FROM hotele

SELECT LOCATE( ' ', address ) pos, address, 
SUBSTRING_INDEX( address, ' ', -1 ) , address REGEXP '[[:digit:]]' 
FROM hotele 
WHERE SUBSTRING_INDEX( address, ' ', -1 ) REGEXP '[[:digit:]]'

update hotele set nr_domu = SUBSTRING_INDEX( address, ' ', -1 ) 
WHERE SUBSTRING_INDEX( address, ' ', -1 ) REGEXP '[[:digit:]]'

SELECT LOCATE( ' ', REVERSE(address) ) pos, address, 
trim(SUBSTRING( address, 1, LENGTH(address) - LOCATE( ' ', REVERSE(address) ))) 
FROM hotele 
WHERE SUBSTRING_INDEX( address, ' ', -1 ) REGEXP '[[:digit:]]'

update hotele set ulica = trim(SUBSTRING( address, 1, LENGTH(address) - LOCATE( ' ', REVERSE(address) ))) 
WHERE SUBSTRING_INDEX( address, ' ', -1 ) REGEXP '[[:digit:]]'