Vedremo in questo articolo qualche query di esempio: query per il confronto tra date, query che coinvolgono colonne con contenuto XML, conversioni di GUID direttamente sul Database ...
Query Delete By Date
Vediamo come fare una delete per eliminare tutti i record antecedenti ad una certa data fissa.
Questa query elimina i record nella tabella "MyTable" del database "MyDB" in cui la data di creazione è precedente alla data specificata, '20190201 00:00:00.000' . La query utilizza il comando DELETE per eliminare i record che soddisfano la condizione specificata nella clausola WHERE. La data viene convertita in un formato di data/ora utilizzando CAST e confrontata con la colonna "CreationDate" della tabella. GO alla fine della query indica il termine della transazione e invia l'istruzione al server:
USE [MyDB]
GO
DELETE FROM [dbo].[MyTable]
WHERE CreationDate < CAST('20190201 00:00:00.000' AS DATETIME)
GO
Query Update Date
Vediamo come fare una query di update per settare tutte le date di una tabella ad un valore fisso.
Questa query modifica i record nella tabella "MyTable" del database "MyDB", in particolare la colonna "UpdateDate" viene impostata su una data specifica '20180101 00:00:00.000' utilizzando il comando UPDATE. La data viene convertita in un formato di data/ora utilizzando CAST e impostata sulla colonna "UpdateDate" di tutti i record presenti nella tabella. Non ci sono condizioni specificate nella clausola WHERE, quindi l'istruzione modificherà tutti i record nella tabella. GO alla fine della query indica il termine della transazione e invia l'istruzione al server:
USE [MyDB]
GO
UPDATE [dbo].[MyTable]
SET [UpdateDate] = CAST('20180101 00:00:00.000' AS DATETIME)
GO
Query On XML Column
Vediamo questa semplice query:
select *
from MyTable
where xmlColumn like '%<myNode>2</myNode>%'
Se provate ad eseguire questa query sicuramente avrete il seguente errore:
Argument data type xml is invalid for argument 1 of like function
Questo perché se nella colonna xmlColumn ci sta del testo in formato XML la precedente sintassi genera un errore.
L'unico modo per eseguire una query di questo tipo è il seguente:
select *
from MyTable
where CAST(xmlColumn AS nvarchar(max)) LIKE N'%<myNode>8</myNode>%'
Ovvero la colonna deve essere prima convertita in testo e poi si esegue la query.
Se ci sono problemi nell'estrazione di contenuti XML troppo grandi inserire il seguente codice alla fine della query:
for xml path
Ad esempio:
select xmlColumn
from MyTable
where CAST(xmlColumn AS nvarchar(max)) LIKE N'%<myNode>8</myNode>%'
for xml path
Anche così facendo si sta usando l'istruzione like quindi la query non risulta molto efficiente.
Una tecnica alternativa è la seguente:
select try_cast(MyField as XML).value('declare namespace PD="http://www.deltavista.com/dspone/ordercheck-if/V001";xpath_specifico', 'nvarchar(max)') as MyFieldDescr
from table
where try_cast(MyField as XML).exist('
declare namespace PD="http://www.deltavista.com/dspone/ordercheck-if/V001";xpath_specifico') = 1
In questo caso si usa un xpath specifico per selezionare il nodo.
La query che hai fornito viene utilizzata per selezionare un campo chiamato MyFieldDescr da una tabella in cui il campo chiamato MyField viene convertito in XML e quindi verificato se contiene un elemento con uno specifico namespace utilizzando il metodo exist(). Il namespace è definito come PD=http://www.deltavista.com/dspone/ordercheck-if/V001
La funzione try_cast() viene utilizzata per convertire un valore in XML. La funzione restituisce NULL se la conversione non riesce.
Conversione in GUID direttamente con SQL
Vediamo questa semplice istruzione per convertire un numero in GUID direttamente in SQL:
cast(CONVERT(BINARY(16), (CONVERT(BINARY(16), numeroDaConvertire))) as uniqueidentifier)
Questa query utilizza una combinazione di funzioni di conversione e di cast per convertire un numero in un valore uniqueidentifier.
La funzione CONVERT viene utilizzata per convertire il numero in un valore BINARY(16) e successivamente CONVERT viene nuovamente utilizzato per convertire nuovamente il valore BINARY(16) in un valore uniqueidentifier utilizzando la funzione CAST.
In generale, questa query converte il numero in un valore univoco in formato binario e quindi lo riconverte in un valore univoco in formato uniqueidentifier.
Nota: questa query potrebbe avere problemi di compatibilità o di performance, dipende dal contesto in cui è utilizzata.
La seguente è una query che trasforma direttamente da GUID a BIGINT
select MyField, REPLACE(MyField, '-', '')
, cast(CONVERT(VARBINARY(16), REPLACE(CONVERT(VARCHAR(36), MyField), '-', ''), 2) as bigint)
from MyTable
Decodificare un attributo BASE64 contenuto in un JSON
Buon lavoro!