Published on

March 3, 2014

Exploring SQL Server: Using FIND_IN_SET Function

When working with SQL Server, there are often situations where we need to search for values within a comma-separated list. One way to achieve this is by using the FIND_IN_SET function.

The FIND_IN_SET function is an inbuilt function in SQL Server that searches for values within a comma-separated list. It returns the index position of the first parameter within the second parameter. This function can be used as an alternative to the IN clause.

Let’s consider the following scenario:

We have two tables: “items” and “sales”. The “items” table has two columns – “item_id” and “item_description”. The “sales” table has three columns – “sales_id”, “item_id”, “sales_date”, and “sales_amount”.

Here is the SQL code to create and populate the tables:

CREATE TABLE items (
  item_id INT,
  item_description VARCHAR(100)
);

CREATE TABLE sales (
  sales_id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT,
  sales_date DATETIME,
  sales_amount DECIMAL(12, 2)
);

INSERT INTO items VALUES (1, 'Television');
INSERT INTO items VALUES (2, 'Mobile');
INSERT INTO items VALUES (3, 'Laptop');

INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-01-01', 1200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-01-02', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-01-09', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-01-29', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-02-11', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-02-16', 1200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-16', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-20', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-20', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (2, '2014-02-22', 200);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (3, '2014-02-24', 1700);
INSERT INTO sales (item_id, sales_date, sales_amount) VALUES (1, '2014-02-24', 1200);

Now, let’s say we want to find all sales made by Mobile and Laptop. We can achieve this using the IN clause, as shown below:

SELECT *
FROM items
NATURAL JOIN sales
WHERE item_description IN ('Mobile', 'Laptop');

Alternatively, we can use the FIND_IN_SET function to achieve the same result:

SELECT *
FROM items
NATURAL JOIN sales
WHERE FIND_IN_SET(item_description, 'Mobile, Laptop');

Both queries will produce the following result:

item_iditem_descriptionsales_idsales_datesales_amount
2Mobile22014-01-02 00:00:00200.00
3Laptop32014-01-09 00:00:001700.00
3Laptop42014-01-29 00:00:001700.00
3Laptop52014-02-11 00:00:001700.00
2Mobile72014-02-16 00:00:00200.00
2Mobile82014-02-20 00:00:00200.00
2Mobile92014-02-20 00:00:00200.00
2Mobile102014-02-22 00:00:00200.00
3Laptop112014-02-24 00:00:001700.00

Technically, it does not matter which option we use – both the solutions produce almost the same results and performance. The FIND_IN_SET function provides an alternate way to search for values within a comma-separated list.

That’s it for this blog post! We explored the FIND_IN_SET function in SQL Server and learned how it can be used to search for values within a comma-separated list. Feel free to experiment with this function in your own SQL Server projects.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.