SQL - Strona 2 - Kody, triki, sztuczki

7 lut 2009

Formatowanie wartości pola w select sql

select CASE WHEN LENGTH(kategoria)>21 THEN SUBSTRING(kategoria,1,22) ELSE kategoria END as kategoria from kategorie
select CASE WHEN LENGTH(kategoria)>21 THEN CONCAT(SUBSTRING(kategoria,1,22), ".") ELSE kategoria END as kategoria from kategorie

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

4 sie 2008

Składnia select into

select * into branze2 from branze

28 lip 2008

Prosty sposób na katakanę w sql

insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ź%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ż%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ą%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ł%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ś%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ń%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ć%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ó%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ą%'
insert into slo_pl(id, slowo) select id,slowo from slo where slowo like '%ę%'

update slo_pl set slowo=replace(slowo,'ź','z')
update slo_pl set slowo=replace(slowo,'ż','z')
update slo_pl set slowo=replace(slowo,'ą','a')
update slo_pl set slowo=replace(slowo,'ł','l')
update slo_pl set slowo=replace(slowo,'ś','s')
update slo_pl set slowo=replace(slowo,'ń','n')
update slo_pl set slowo=replace(slowo,'ć','c')
update slo_pl set slowo=replace(slowo,'ó','o')
update slo_pl set slowo=replace(slowo,'ę','e')

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