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])
by Amol
26. August 2013 20:32
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"