Difference between revisions of "MSSQL:Funzioni base"
From Aino Wiki
(→IIF) |
(No difference)
|
Latest revision as of 18:52, 25 January 2023
Contents
DATE
GETDATE() Restituisce la data attuale
DATEPART() Restituisce una parte della data, es minuti, ore, secondi etc. che sono info in più che dà rispetto a DAY(), MONTH(), YEAR(). Sintassi: DATEPART(datepart,date)
GETUTCDATE() Restituisce la data universale attuale
BETWEEN verifica il range tra due valori (è usata oltre le date) segue Es.
Notare anche la somma di date espressa in minuti:
DECLARE @HistoryRangeNinutes AS INT = -100, @Date_FROM AS DatetimeOffset(7) = GETUTCDATE(), @Date_TO AS DatetimeOffset(7) SET @Date_TO = DATEADD(MINUTE, @HistoryRangeNinutes, @Date_FROM) SELECT TOP 100 A.[Id] ,A.[CompanyCode] ,A.[ShipCode] ,A.[ThresholdId] ,A.[Type] ,A.[Triggered] ,A.[Status] ,@Date_TO AS [TO] ,A.[TriggerDateUTC] ,@Date_FROM AS [FROM] ,A.[NotifiedTo] FROM [Alerts] AS A (NOLOCK) WHERE A.[TriggerDateUTC] BETWEEN @Date_TO AND @Date_FROM AND A.[NotifiedTo] IS NULL
Differenza, somma di date
Si può eseguire somma o differenza di date secondo l'unità di misura indicata come negli esempi:DECLARE @HistoryRangeNinutes AS INT = -100, @Date_FROM AS DatetimeOffset(7) = GETUTCDATE(), @Date_TO AS DatetimeOffset(7) SET @Date_TO = DATEADD(MINUTE, @HistoryRangeNinutes, @Date_FROM) -- Differenza: DATEDIFF ( datepart , startdate , enddate ) SELECT DATEDIFF (MINUTE, @Date_TO, @Date_FROM) -- ^-- Restituirà: 100 SELECT DATEDIFF (MINUTE, @Date_FROM, @Date_TO) -- ^-- Restituirà: -100 SELECT ABS(DATEDIFF (MINUTE, @Date_FROM, @Date_TO)) -- ^-- Restituirà: 100
Funzioni di comparazione
COALESCE
T-SQL ci fornisce la funzione ISNULL ma non è standard meglio usare la Coalesce. Vedere w3school
Cosa utilissima è che si possono usare n-parametri in cascata si valuteranno nell'ordine e si sceglierà il primo non NULL.
SELECT COALESCE(campo1, campo2) AS campo, COALESCE(A.[ShipName], A5.[ShipName], M.Ship_Name) AS ShipName FROM MiaTabella UNO LEFT JOIN T2 AS A ... LEFT JOIN T3 AS A5 ... LEFT JOIN T4 AS M ...
ISNULL(campoTest, valoreOCampoSeNulloCampoTest)
SELECT ISNULL(campo1, campo2) AS campo FROM MiaTabella
Simulare ISNULL ELSE
Nel caso si debba fare IF ... THEN ELSE su una variabile sia nulla si può usare il CASE
statement:
SELECT CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END FROM ...
Caso realistico usato nella WHERE condition:
DECLARE @LastIDImported AS VARCHAR(100), -- = '2018-11-08 16:20:32.0000000', @SQLQuery AS VARCHAR(max) SET @SQLQuery = 'SELECT TOP 10 * FROM [AACC_DB]..[cbc].[NIeCallByCallStat20181108] WHERE statTimestamp > ''' + ISNULL(@LastIDImported, '2100-01-01 00:00:00') + ''' OR ' + (CASE WHEN @LastIDImported IS NULL THEN '1=1' ELSE '1=2' END) + ' ORDER BY statTimestamp DESC' EXEC (@SQLQuery)
NullIF
Restituisce NULL nel caso i due argomenti sono uguali. Serve nel caso si facciano conversioni.
NULLIF ( expression , expression )
Es:
SELECT NULLIF(@StrDaImportare, ''); -- I campi con stringa vuota convertiti in NULL ---------------------------------------------- SELECT COALESCE(CONVERT(varchar(20), NullIf(CONVERT([varchar](50), [Campo in tabella]) ,0x20) ) ,' ')
IIF
E' un IF THEN ELSE compatto che si può usare nelle SELECT.
IIF(condition, value_if_true, value_if_false)
SELECT IIF(100<200, 'YES', 'NO'); --Restituirà YES SELECT IIF('Saluto' = 'Ciao', 'YES', 'NO'); --Restituirà NO
Funzioni Matematiche
Divisione decimale
Come ottenere un valore decimale (DECIMAL) dalla divisione di due numeri interi:
SELECT DATEDIFF(MINUTE, @DataUNO, @DataDUE) / CONVERT(DECIMAL(10,2), @ipotesiDurataSegmento) * 100 AS X1
Esempio più complesso:
SELECT CB.[Segment1Id], CB.[uid], DATEDIFF(SECOND, CB.[on_UTCDate], CB.[off_UTCDate]) AS AlarmDurationSeconds, DATEDIFF(SECOND, S.[ETD], S.[ETA]) AS SegmentDurationSeconds, CB.Severity_type --ARROTONDAMENTO ,ROUND( ((DATEDIFF(SECOND, CB.[on_UTCDate], CB.[off_UTCDate]) ) / CONVERT(FLOAT, DATEDIFF(SECOND, S.[ETD], S.[ETA]))) * 49.9 + (50.0 * (CAST(CB.[severity_type] AS FLOAT) - 1)), 0) FROM [Fact_CorridorBreach] AS CB (NOLOCK) INNER JOIN @Dim_Segments AS S ON CB.[Segment1Id] = S.Id WHERE CB.[severity_type] > 0
Esponenziale
EXP
L'equivalente di e^10 dove e è il numero naturale: e = 2.71828182845905quindi:EXP(1.0) = e^1.0 = 2.71828182845905
SELECT EXP(10)
produce
22026,4657948067
POWER
Ha due argomenti il primo è la base ed il secondo è l'esponente. Ecco l'equivalente di 10^3:SELECT POWER(10, 3)
produce
1000
Per avere il risultato di: 10^-10
SELECT (1.0 / POWER(10, 9))/ 10
produce
0.000000000100000
Arrotondamento
ROUND
Usando la ROUND, sintassiROUND ( numeric_expression , length [ ,function ] )
ricorda la sintassi di decimal[ (p[ ,s] )] dove 'p' è il numero totale di cifre sia a sx che a dx della virgola e 's' è il numero di cifre a dx della virgola Esempio:
DECLARE @value1 AS decimal(11, 8) = 123.9994, @value2 AS decimal(11, 8) = 123.9995 SELECT ROUND(@value1, 3), ROUND(@value2, 3) -- 123.99900000 124.00000000 SELECT ROUND(123.9994, 3), ROUND(123.9995, 3) -- 123.99900000 124.00000000
Altro
DECLARE @value1 AS decimal(11, 8) = 123.4, @value2 AS decimal(11, 8) = 123.5 SELECT ROUND(@value1, 0), ROUND(@value2, 0) -- 123 124
Funzioni sulle Query
EXISTS
Metodo rapido per verificare se un record esiste:
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOL\DefaultAppPool') BEGIN CREATE LOGIN [IIS APPPOOL\DefaultAppPool] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] END
EXISTS di una tabella
L'approccio migliore è interrogare la tabella di sistema INFORMATION_SCHEMA
, attenzione occorre avere già impostato il database su cui fare la ricerca (es.: USE mioDatabase; ):
DECLARE @TABLE_NAME AS VARCHAR(50) = 'PO_B_ORDER_TYPE_EXT_NZL' IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = @TABLE_NAME)) BEGIN Print 'Table ' + @TABLE_NAME + ' already created' END ELSE BEGIN Print 'Table ' + @TABLE_NAME + ' will be created' END
EXISTS di una tabella Temporanea #
DECLARE @TABLE_NAME AS VARCHAR(50) = '' SET @TABLE_NAME = 'tempdb..#SOI' if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(@TABLE_NAME) ) BEGIN Print @TABLE_NAME + ' exists, dropping' DROP TABLE #SOI; END ELSE BEGIN Print @TABLE_NAME + ' NOT exists' END
Stringhe
Vedi anche: [Operazioni sulle stringhe]
Elaborazioni
LEN
Per calcolare la lunghezza di una stringa usare LEN(string_expression )
, restituisce un intero.
SELECT LEN('12345') --Restituisce 5
REPLACE
Per sostituzioni o puliziie di stringhe: REPLACE ( string_expression , string_pattern , string_replacement )
.
SELECT REPLACE('abcdefghicde','cde','xxx');
Restituisce
abxxxfghixxx
Esempi Realistici!
Esempio su una stringa CSV per estrarre un parametro
DECLARE @Script_res AS varchar(255) = 'w;5;0;350444;0;0;0;0;' --Esecuzione a cascata: SELECT REPLACE( REPLACE( REPLACE( @Script_res , ';0;0;0;0;', '') , 'w;5;0;', '') , 'w;10;0;', '')
Restituisce
350444
Estrazione Substringhe
SUBSTRING
La posizione è contata in BASE 1, SUBSTRING ( expression ,start , length )
--La seguente restituirà 1 SELECT SUBSTRING('012345', 2, 1) --La seguente restituirà '' SELECT SUBSTRING(ISNULL(NULL, ''), 2, 1)
Esempio realistico di elaborazione di una stringa, contenente codice SQL, da cui si toglie la prima condizione in AND.
DECLARE @SQLWhereStatement AS nvarchar(max) = 'AND [WAITBEGIN] > CONVERT(DateTime,''2019-01-06 23:40:54'', 120) AND ROUND((CALLEND - CALLBEGIN)*24*60*60) >=15 AND (CALLEENO =''44449905'' OR CALLEENO LIKE ''455%'' OR NVL(ORGCALLEENO,CALLEENO) LIKE ''499%'' OR CALLEENO LIKE ''4752455%'')', @At AS int = 0 Print 'Prima ' + @SQLWhereStatement SET @At = CHARINDEX('AND', @SQLWhereStatement, 2) Print ' Posizione: ' + CAST(@At AS varchar) SET @SQLWhereStatement = SUBSTRING(@SQLWhereStatement, @AT, LEN(@SQLWhereStatement) - @At) Print ' Dopo ' + @SQLWhereStatement
RIGHT - LEFT
Restituisce la parte finale di una stringa di caratteri, di lunghezza pari al numero di caratteri specificato. Questa è la sintassi:
RIGHT ( character_expression , integer_expression )
character_expression
E' il campo stringa su cui lavora.
integer_expression
È un numero intero positivo che specifica il numero di caratteri di character_expression verrà restituito.
DECLARE @testo AS varchar(max) = '09876543210' SELECT RIGHT(@testo, 5) -- restituisce: 43210
Se la stringa è minore di 5 caratteri restituirà fintanto che ce n'è!!!
Trim Rimozione caratteri
Spazi
Per rimuovere spazi all'inizio ed alla fine TRIM ( [ characters FROM ] string )
Esistono anche le LTRIM
e RTRIM
SELECT TRIM(' test ') AS Result; -- Dalla versione 2017 !!!!!!!! ---- equivalente a: SELECT LTRIM(RTRIM( ' test ')) AS Result;
Caratteri
Solo dalla versione 2017. Rimuove un punto finale e spazi finali.
SELECT TRIM( '.,! ' FROM '# test .') AS Result;;
Restituisce
# test
Ricerche
CHARINDEX
CHARINDEX('_', stringvalue, startpos)
E' in base 1 ovvero se non trova occorrenze restituisce 0, se la trova all'inizio della stringa restituirà 1.
if CHARINDEX('ME',@mainString) > 0 begin --do something end ------------etc SELECT [ShipNameRefer] ,[MMSI] ,[NavigationStatus] ,[Speed] ,CAST(CAST((CAST([Speed] AS DECIMAL(10, 1)) * 10) AS integer) AS NVARCHAR(100)) AS SpeedOk ,[LastUpdateUTC] ,[IMO_Number] ,[Source] FROM [RouteMonitoring].[dbo].[AIVDM] (nolock) WHERE [ShipNameRefer] = 'BL' --AND LastUpdateUTC BETWEEN '2016-09-13 07:20:53.000' AND '2016-09-13 07:31:11.000' AND CHARINDEX('.', [Speed]) > 0 ORDER BY LastUpdateUTC DESC
Mappa e Link
MS SQL | Comandi di Query | Tips