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

Per avere il risultato di: 10^-10

SELECT (1.0 / POWER(10, 9))/ 10

produce

0.000000000100000

Arrotondamento

ROUND

Usando la ROUND, sintassi

ROUND ( 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

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.

Esempio

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

Navigation

Help

Tools

WIDGETS

Widgets

Recent changes

Wanted articles

Who is online?

Article tools

MSSQL:Funzioni base

From Aino Wiki

Contents

DATE

Differenza, somma di date

Funzioni di comparazione

COALESCE

ISNULL(campoTest, valoreOCampoSeNulloCampoTest)

Simulare ISNULL ELSE

NullIF

IIF

Funzioni Matematiche

Divisione decimale

Esponenziale

EXP

POWER

Arrotondamento

ROUND

Funzioni sulle Query

EXISTS

EXISTS di una tabella

EXISTS di una tabella Temporanea #

Stringhe

Elaborazioni

LEN

REPLACE

Estrazione Substringhe

SUBSTRING

RIGHT - LEFT

Trim Rimozione caratteri

Spazi

Caratteri

Ricerche

CHARINDEX

Mappa e Link