{"id":413158,"date":"2024-02-21T21:40:02","date_gmt":"2024-02-22T05:40:02","guid":{"rendered":"https:\/\/linuxhint.com\/?p=413158"},"modified":"2024-02-24T11:42:21","modified_gmt":"2024-02-24T19:42:21","slug":"sql-contains-string","status":"publish","type":"post","link":"https:\/\/linuxhint.com\/sql-contains-string\/","title":{"rendered":"SQL String Contains"},"content":{"rendered":"
\nString operations is a common task in the world of development. When it comes to databases, you will encounter yourself needing to search or manipulate the text information.<\/p>\n

One common operation is searching for a specific substring or pattern within a text value, expression, or column. This is also known as \u201cstring contains\u201d.<\/p>\n

String contains mainly comes up when you need to filter or retrieve the data that follows a specific text pattern.<\/p>\n

In this tutorial, we will explore the various methods and techniques that you can use to search whether a string contains another substring. It is good to keep in mind that each database provides different methods for the same operation. It is therefore good to consult the document for your desired database engine to learn more.<\/p>\n

General Syntax:<\/h2>\n

Before we dive into the various methods, we can attempt to establish a common syntax that can summarize how the string contains the operation works in SQL.<\/p>\n

Take a look at the following example:<\/p>\n

SELECT * FROM table_name
\n
\nWHERE column_name CONTAINS 'substring_to_search';<\/div><\/div>\n

You will notice that we use the WHERE clause to establish a search based on the text that matches the specified pattern.<\/p>\n

The CONTAINS on this case varies on the target database engine. For example, you may need to replace it with functions such as EXTRACT, SUBSTRING, and more.<\/p>\n

Again, this heavily depends on your database engine.<\/p>\n

Method 1: LIKE Operator<\/h2>\n

The first and most common operator that we can use to check whether a given string contains another substring is the LIKE operator. This operator allows us to perform the pattern-based matching using the wildcard characters.<\/p>\n

Take for example the following code that demonstrates how to use the \u201c%\u201d wildcard character to search whether a string contains another:<\/p>\n

SELECT * FROM products
\n
\nWHERE product_name LIKE '%apple%';<\/div><\/div>\n

This query returns all rows from the \u201cproducts\u201d table where the \u201cproduct_name\u201d column contains the word “apple” anywhere within the string.<\/p>\n

Another example is using the \u201c_\u201d as the wildcard character. Take a look at the following code:<\/p>\n

SELECT * FROM products
\n
\nWHERE product_name LIKE 'Ap_le';<\/div><\/div>\n

In this case, the query returns the rows where the \u201cproduct_name\u201d column contains any single character in the place of the \u201c_\u201d character to. An example is something like \u201cApple\u201d.<\/p>\n

Method 2: CHARINDEX()<\/h2>\n

In SQL Server, we have access to the CHARINDEX() function that allows us to find the position of a substring within a given string. If the result is greater than 0, it means that the substring is within the specified string.<\/p>\n

An example is as follows:<\/p>\n

SELECT * FROM products
\n
\nWHERE CHARINDEX('apple', product_name) > 0;<\/div><\/div>\n

This query selects the rows from the \u201cproducts\u201d table where the \u201cproduct_name\u201d column contains the word “apple”.<\/p>\n

Oracle also supports this function but it is under the name INSTR() function as shown in the following example:<\/p>\n

SELECT * FROM products
\n
\nWHERE INSTR(product_name, 'apple') > 0;<\/div><\/div>\n

This should return the matching substring.<\/p>\n

Method 3: POSITION()<\/h2>\n

In PostgreSQL, we can use the POSITION() function to check for the existence of a substring within another string.<\/p>\n

SELECT * FROM products
\n
\nWHERE POSITION('apple' IN product_name) > 0;<\/div><\/div>\n

This works very similarly to the CHARINDEX() function in SQL Server.<\/p>\n

Conclusion<\/h2>\n

In this tutorial, we learned how we can use the various SQL methods and techniques to check whether a string contains a specified substring.\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"

Practical guide on how we can use the various SQL methods and techniques to check or search whether a string contains a specified substring along with examples.<\/p>\n","protected":false},"author":111,"featured_media":413211,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_wp_convertkit_post_meta":{"form":"-1","landing_page":"","tag":"0","restrict_content":"0"},"footnotes":""},"categories":[2451],"tags":[],"class_list":["post-413158","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-standard"],"_links":{"self":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/posts\/413158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/users\/111"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/comments?post=413158"}],"version-history":[{"count":0,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/posts\/413158\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/media\/413211"}],"wp:attachment":[{"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/media?parent=413158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/categories?post=413158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxhint.com\/wp-json\/wp\/v2\/tags?post=413158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}