Cómo usar una cadena nula o vacía en SQL
Me gustaría saber cómo usar NULL y una cadena vacía al mismo tiempo en una cláusula WHERE
en SQL Server. Necesito encontrar registros que tengan valores nulos o una cadena vacía. Gracias.
15 answers
Select *
From Table
Where (col is null or col = '')
O
Select *
From Table
Where IsNull(col, '') = ''
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-27 15:55:01
Simplemente puedes hacer esto:
SELECT *
FROM yourTable
WHERE yourColumn IS NULL OR yourColumn = ''
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-27 15:54:37
Si lo necesitas en la sección SELECT puedes usarlo así.
SELECT ct.ID,
ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM [dbo].[CustomerTable] ct
Puede reemplazar el null
con su valor de sustitución.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-10 08:12:17
SELECT *
FROM TableName
WHERE columnNAme IS NULL OR
LTRIM(RTRIM(columnName)) = ''
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-27 15:53:09
Para encontrar filas donde col es NULL
, cadena vacía o espacio en blanco (espacios, tabulaciones):
SELECT *
FROM table
WHERE ISNULL(LTRIM(RTRIM(col)),'')=''
Para encontrar filas donde col es NOT NULL
, cadena vacía o espacio en blanco (espacios, tabulaciones):
SELECT *
FROM table
WHERE ISNULL(LTRIM(RTRIM(col)),'')<>''
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-10-09 03:32:18
Algunos métodos sargable...
SELECT *
FROM #T
WHERE SomeCol = '' OR SomeCol IS NULL;
SELECT *
FROM #T
WHERE SomeCol = ''
UNION ALL
SELECT *
FROM #T
WHERE SomeCol IS NULL;
SELECT *
FROM #T
WHERE EXISTS ((SELECT NULL UNION SELECT '') INTERSECT SELECT SomeCol);
Y algunos no sargable queridos...
SELECT *
FROM #T
WHERE IIF(SomeCol <> '',0,1) = 1;
SELECT *
FROM #T
WHERE NULLIF(SomeCol,'') IS NULL;
SELECT *
FROM #T
WHERE ISNULL(SomeCol,'') = '';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:54:44
Esto es feo MSSQL:
CASE WHEN LTRIM(RTRIM(ISNULL([Address1], ''))) <> '' THEN [Address2] ELSE '' END
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-09-29 16:37:24
SELECT *
FROM Table
WHERE column like '' or column IS NULL OR LEN(column) = 0
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-27 15:55:19
Usted podría utilizar isnull
función para obtener tanto null
como valores vacíos de un campo de texto:
SELECT * FROM myTable
WHERE isnull(my_nullable_text_field,'') = ''
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-03-27 15:59:08
--setup
IF OBJECT_ID('tempdb..#T') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, NAME VARCHAR(10))
INSERT INTO #T (Name) VALUES('JOHN'),(''),(NULL);
SELECT * FROM #T
1 JOHN
2 -- is empty string
3 NULL
Puede examinar ''
como NULL
convirtiéndolo a NULL
usando NULLIF
--here you set '' to null
UPDATE #T SET NAME = NULLIF(NAME,'')
SELECT * FROM #T
1 JOHN
2 NULL
3 NULL
O puedes examinar NULL
como ''
usando SELECT ISNULL(NULL,'')
-- here you set NULL to ''
UPDATE #T SET NAME = ISNULL(NULL,'') WHERE NAME IS NULL
SELECT * FROM #T
1 JOHN
2 -- is empty string
3 -- is empty string
--clean up
DROP TABLE #T
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-07 14:23:17
Mi mejor solución :
WHERE COALESCE(char_length(fieldValue), 0) = 0
COALESCE devuelve el primer expr no nulo en la expresión list().
Si el FieldValue es null o cadena vacía entonces: devolveremos el segundo elemento luego 0.
Así que 0 es igual a 0, entonces este valor de campo es nulo o cadena vacía.
En python por ejemplo:
def coalesce(fieldValue):
if fieldValue in (null,''):
return 0
Buena suerte
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-02-16 14:55:27
En sproc, puede usar la siguiente condición:
DECLARE @USER_ID VARCAHR(15)=NULL --THIS VALUE IS NULL OR EMPTY DON'T MATTER
IF(COALESCE(@USER_ID,'')='')
PRINT 'HUSSAM'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-11 08:24:05
Por esta función:
ALTER FUNCTION [dbo].[isnull](@input nvarchar(50),@ret int = 0)
RETURNS int
AS
BEGIN
return (case when @input='' then @ret when @input is null then @ret else @input end)
END
Y usa esto:
Dbo.isnull (valor,0)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-05 11:32:15
SELECT * FROM DBO.AGENDA
WHERE
--IF @DT_START IS NULL OR EMPTY
( ISNULL( @DT_START,'' ) = '' AND DT_START IS NOT NULL ) -- GET ALL DATE
OR --ELSE
( DT_START >= @DT_START ) --FILTER
-- MORE FILTER
SELECT * FROM DBO.AGENDA
WHERE
( ( ISNULL( @DT_START,'' ) = '' AND DT_START IS NOT NULL ) OR ( DT_START >= @DT_START ) )
AND
DT_END < GETDATE()
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-18 20:06:49
Usted comprueba null Con ES NULL y cadena Vacía Con LEN(RTRIM(LTRIM (Columna))) = 0 en
SELECT *
FROM AppInfra.Person
WHERE LEN(RTRIM(LTRIM(NationalCode))) = 0 OR NationalCode IS NULL
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-08-13 12:40:59