Clausola CASE T-SQL: come specificare WHEN NULL

Ho scritto una dichiarazione T-SQL simile a questa (l’originale sembra diversa ma voglio dare un esempio semplice qui):

SELECT first_name + CASE last_name WHEN null THEN 'Max' ELSE 'Peter' END AS Name FROM dbo.person 

Questa dichiarazione non ha errori di syntax ma la clausola case sceglie sempre la parte ELSE – anche se il cognome è nullo. Ma perché?

Quello che voglio fare è unire first_name e last_name, ma se last_name è null l’intero nome diventa null:

 SELECT first_name + CASE last_name WHEN null THEN '' ELSE ' ' + last_name END AS Name FROM dbo.person 

Sai dov’è il problema?

 CASE WHEN last_name IS NULL THEN '' ELSE ' '+last_name END 

La parte WHEN viene confrontata con ==, ma non si può davvero confrontare con NULL. Provare

 CASE WHEN last_name is NULL THEN ... ELSE .. END 

invece o COALESCE:

 COALESCE(' '+last_name,'') 

(” + last_name è NULL quando last_name è NULL, quindi dovrebbe restituire ” in tal caso ‘

Data la tua richiesta puoi anche fare questo:

 SELECT first_name + ' ' + ISNULL(last_name, '') AS Name FROM dbo.person 

Il problema è che null non è considerato uguale a se stesso, quindi la clausola non corrisponde mai.

È necessario verificare in modo esplicito null:

 SELECT CASE WHEN last_name is NULL THEN first_name ELSE first_name + ' ' + last_name 

Ci sono molte soluzioni, ma nessuna copre il motivo per cui la dichiarazione originale non funziona.

 CASE last_name WHEN null THEN '' ELSE ' '+last_name 

Dopo il quando, c’è un controllo per l’uguaglianza, che dovrebbe essere vero o falso.

Se una o entrambe le parti di un confronto sono nulle, il risultato del confronto sarà UNKNOWN, che viene trattato come falso in una struttura del caso. Vedi: https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

Per evitare questo, Coalesce è il modo migliore.

provare:

 SELECT first_name + ISNULL(' '+last_name, '') AS Name FROM dbo.person 

Questo aggiunge lo spazio al cognome, se è nullo, l’intero spazio + il cognome va a NULL e si ottiene solo un nome, altrimenti si ottiene un primo + spazio + cognome.

questo funzionerà finché viene impostata l’impostazione predefinita per la concatenazione con stringhe null:

 SET CONCAT_NULL_YIELDS_NULL ON 

questo non dovrebbe essere un problema dato che la modalità OFF sta andando via nelle future versioni di SQl Server

Il problema è che NULL non è considerato uguale a nulla nemmeno a se stesso, ma la parte strana è che non è uguale a se stesso.

Considerare le seguenti dichiarazioni (che è BTW illegale in SQL Server T-SQL ma è valida in My-SQL, tuttavia questo è ciò che ANSI definisce per null e può essere verificato anche in SQL Server utilizzando le istruzioni case ecc.)

SELECT NULL = NULL -- Results in NULL

SELECT NULL <> NULL -- Results in NULL

Quindi non c’è una risposta vera / falsa alla domanda, invece la risposta è anche nulla.

Questo ha molte implicazioni, per esempio in

  1. Istruzioni CASE, in cui qualsiasi valore nullo utilizzerà sempre la clausola ELSE a meno che non si utilizzi esplicitamente la condizione WHEN IS NULL ( NON la condizione WHEN NULL )
  2. Concatenazione di stringhe, come
    SELECT a + NULL -- Results in NULL
  3. In una clausola WHERE IN o WHERE NOT IN, come se si desidera che i risultati corretti si assicurino nella subquery correlata di filtrare eventuali valori nulli.

È ansible sovrascrivere questo comportamento in SQL Server specificando SET ANSI_NULLS OFF , tuttavia questo NON è consigliato e non dovrebbe essere eseguito in quanto può causare molti problemi, semplicemente a causa della deviazione dello standard.

(Come nota a margine, in My-SQL esiste un’opzione per utilizzare un operatore speciale <=> per il confronto con null.)

In confronto, in generale i linguaggi di programmazione null è trattato è un valore regolare ed è uguale a se stesso, tuttavia è il valore NAN che non è uguale a se stesso, ma almeno restituisce ‘false’ quando lo si confronta con se stesso, (e quando si cerca di non eguagliare diversi linguaggi di programmazione hanno implementazioni differenti).

Si noti tuttavia che nelle lingue di base (es. VB ecc.) Non esiste una parola chiave ‘null’ e invece si usa la parola chiave ‘Nothing’, che non può essere utilizzata nel confronto diretto e invece si deve usare ‘IS’ come in SQL, tuttavia è di fatto uguale a se stesso (quando si usano confronti indiretti).

 CASE WHEN last_name IS null THEN '' ELSE ' ' + last_name END 

Jason ha rilevato un errore, quindi questo funziona …

Qualcuno può confermare le altre versioni della piattaforma?
Server SQL:

 SELECT CASE LEN(ISNULL(last_name,'')) WHEN 0 THEN '' ELSE ' ' + last_name END AS newlastName 

MySQL:

 SELECT CASE LENGTH(IFNULL(last_name,'')) WHEN 0 THEN '' ELSE ' ' + last_name END AS newlastName 

Oracolo:

 SELECT CASE LENGTH(NVL(last_name,'')) WHEN 0 THEN '' ELSE ' ' + last_name END AS newlastName 

Quando ti senti frustrato provando questo:

 CASE WHEN last_name IS NULL THEN '' ELSE ' '+last_name END 

Prova questo invece:

 CASE LEN(ISNULL(last_Name,'')) WHEN 0 THEN '' ELSE ' ' + last_name END AS newlastName 

LEN(ISNULL(last_Name,'')) misura il numero di caratteri in quella colonna, che sarà zero se è vuoto, o NULL, quindi WHEN 0 THEN valuterà in vero e restituirà il ” come previsto.

Spero che questa sia un’alternativa utile.

Ho incluso questo test case per SQL Server 2008 e versioni successive:

 DECLARE @last_Name varchar(50) = NULL SELECT CASE LEN(ISNULL(@last_Name,'')) WHEN 0 THEN '' ELSE 'A ' + @last_name END AS newlastName SET @last_Name = 'LastName' SELECT CASE LEN(ISNULL(@last_Name,'')) WHEN 0 THEN '' ELSE 'A ' + @last_name END AS newlastName 

È ansible utilizzare la funzione IsNull

 select isnull(rtrim(ltrim([FirstName]))+' ','') + isnull(rtrim(ltrim([SecondName]))+' ','') + isnull(rtrim(ltrim([Surname]))+' ','') + isnull(rtrim(ltrim([SecondSurname])),'') from TableDat 

se una colonna è nullo si otterrebbe un carattere vuoto

Compatibile con Microsoft SQL Server 2008+

Utilizzare la funzione CONCAT disponibile in SQL Server 2012 in poi.

 SELECT CONCAT([FirstName], ' , ' , [LastName]) FROM YOURTABLE 

Ho provato a eseguire il casting su una stringa e a testare una stringa di lunghezza zero e ha funzionato.

 CASE WHEN LEN(CAST(field_value AS VARCHAR(MAX))) = 0 THEN DO THIS END AS field 

NULL non equivale a nulla. L’affermazione del caso dice fondamentalmente quando il valore = NULL … non colpirà mai.
Esistono anche diverse stored procedure di sistema che sono state scritte in modo errato con la syntax. Vedi sp_addpullsubscription_agent e sp_who2.
Vorrei sapere come comunicare a Microsoft gli errori in quanto non sono in grado di modificare i proc memorizzati dal sistema.