Select - Strona 4 - Kody, triki, sztuczki

4 wrz 2008

REGEXP - dopasowanie przez wyrażenie regularne

SELECT miasto from miasta where miasto REGEXP '^opole'

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:]]'

28 lip 2008

Odczyt wartości zwracanej przez procedurę

DECLARE @RC int EXEC @RC = [baza].[dbo].[procedura_lista] DECLARE @PrnLine nvarchar(4000) PRINT 'Stored Procedure' SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC) PRINT @PrnLine

4 lip 2008

Ustawienie pola Select i Checkbox

function ustawNazwePozycji(form, fld, name) {
  idx = fld.selectedIndex;
  text = fld.options[idx].text;
  fld_name = form.elements[name];
  fld_name.value = text; 
  form.elements['check_link'].checked = false;
}

1 lip 2008

Wyświetlanie pełnej treści pola znakowego

declare @a nvarchar(1000) set @a = (select top 1 parametr from tablica where id=300) print @a

Ominięcie bugu serwera