Use NOT IN operator carefully

by Amol 25. May 2014 20:50
Whenever you use NOT IN operator you will have to be more conscious. Following example will explain why we need to be careful while using NOT IN operator.
  
 
CREATE TABLE [dbo].[Languages]
(  
    Id INT IDENTITY(1,1),
    [Language] VARCHAR(256)
)
GO
CREATE TABLE [dbo].[Books]
(  
    ISBN INT,
    [Language] VARCHAR(256)
)
GO
INSERT INTO [dbo].[Languages]
SELECT 'English' UNION ALL
SELECT 'Hindi' UNION ALL
SELECT 'Spanish' UNION ALL
SELECT 'French' UNION ALL
SELECT 'German' 
GO
INSERT INTO [dbo].[Books]
SELECT 1,'English'
UNION ALL SELECT 2,'Spanish'
UNION ALL SELECT 3,NULL
GO
SELECT l.[Language]
FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT [Language] FROM [dbo].[Books]);
 
 
 
Language
----------------------------------------------------
(0 row(s) affected)
 
 
 

   

0 rows will be returned when you execute the above statements. And this not the correct answer. This is because ultimately NOT IN operator is equivalent to

"NOT (Language = English OR Language = Spanish OR Language = NULL)

If you need the correct answer you will have to convert the NULLs into blank.



SELECT l.[Language]
FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT ISNULL(l.[Language], '') FROM [dbo].[Languages])
OR

SELECT l.[Language]

FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT ISNULL([Language], '') FROM [dbo].[Books])
OR
  
SELECT l.[Language]
FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT [Language] FROM [dbo].[Books] WHERE [Language] IS NOT NULL)
OR

 

SELECT l.[Language]

FROM [dbo].[Languages] AS l
WHERE NOT EXISTS (SELECT * FROM [dbo].[Books] WHERE [Language] = l.[Language])

Tags:

General

T-SQL to get string between two strings

by Amol 17. July 2013 09:17

Below is the user defined function created in SQL Server to find the string between two strings.

 

CREATE FUNCTION dbo.udf_GetStringBetween2Strings 
(
	@String varchar(max), 
	@FirstString varchar(256), 
	@SecondString varchar(256)
)
RETURNS VARCHAR(max) 
AS
BEGIN

	DECLARE @FirstPosition int,  @SecondPosition int
	SET @FirstPosition = CHARINDEX(@FirstString,@String) + LEN(@FirstString)
	SET @SecondPosition = CHARINDEX(@SecondString,@String) 
	RETURN (SELECT SUBSTRING(@String, @FirstPosition, @SecondPosition - @FirstPosition))

END
GO

DECLARE @MyString varchar(256) = 'Bla bla bla xyxz Find my name Amol Rajmane in this string.'
SELECT dbo.udf_GetStringBetween2Strings (@MyString, 'Find my name ', ' in this') 
GO

 

 

Result is "Amol Rajmane"

Tags: ,

General

Load Delimited CSV file using ACE Driver

by Amol 14. December 2012 10:20

ACE driver will read the comma-delimited file without any issues. But if you have different delimiter it doesn’t work as expected.

Earlier we used to use FORMAT=Delimited(|) as a switch in connection string to load different delimiter CSV file.

 

To read the file with different delimiter, you need to create Schema.ini file in the same directory as the SCV file. And add the below contents in it.

 

[SampleFile.csv]

Format=Delimited(|)

ColNameHeader=False

 

Once you ready with the Schema.ini file run the below command to read the CSV file using ACE driver

 

SELECT TOP *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\Temp\;HDR=No',

'SELECT * FROM [SampleFile.csv]')

 

Tags: , ,

General | SQL Server Scripts

Category

Recent Posts

Tag cloud