CODEWITHSUNDEEP
sqlnode.jssql-servert-sqlsql-like
Amandeep Singh
Amandeep Singh

Reputation: 41

Apply like function on an array is SQL Server

I am getting array from front end to perform filters according that inside the SQL query.

I want to apply a LIKE filter on the array. How to add an array inside LIKE function?

I am using Angular with Html as front end and Node as back end.

Array being passed in from the front end:

 [ "Sports", "Life", "Relationship", ...]

SQL query is :

SELECT *
FROM Skills
WHERE Description LIKE ('%Sports%')

SELECT *
FROM Skills
WHERE Description LIKE ('%Life%')

SELECT *
FROM Skills
WHERE Description LIKE ('%Relationship%')

But I am getting an array from the front end - how to create a query for this?

Upvotes: 3

Views: 3113

Answers (3)

Peter B
Peter B

Reputation: 24147

A simple map()-call on the words array will allow you to generate the corresponding queries, which you can then execute (with or without joining them first into a single string).

Demo:

var words = ["Sports", "Life", "Relationship"];
var template = "Select * From Skills Where Description Like ('%{0}%')";

var queries = words.map(word => template.replace('{0}', word));
var combinedQuery = queries.join("\r\n");

console.log(queries);
console.log(combinedQuery);

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

In SQL Server 2017 you can use OPENJSON to consume the JSON string as-is:

SELECT *
FROM skills
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON('["Sports", "Life", "Relationship"]', '$') AS j
    WHERE skills.description LIKE '%' + j.value + '%'
)

Demo on db<>fiddle

Upvotes: 3

Alexander Volok
Alexander Volok

Reputation: 5940

As an example, for SQL Server 2016+ and STRING_SPLIT():

DECLARE @Str NVARCHAR(100) = N'mast;mode'

SELECT name FROM sys.databases sd
INNER JOIN STRING_SPLIT(@Str, N';') val ON sd.name LIKE N'%' + val.value + N'%'

-- returns:

name
------
master
model

Worth to mention that input data to be strictly controlled, since such way can lead to SQL Injection attack

As the alternative and more safer and simpler approach: SQL can be generated on an app side this way:

 Select * from Skills 
 WHERE (
     Description Like '%Sports%' 
 OR  Description Like '%Life%' 
 OR  Description Like '%Life%' 
     )

Upvotes: 0

Related Questions