{"id":23315,"date":"2018-08-13T07:05:06","date_gmt":"2018-08-13T07:05:06","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=23315"},"modified":"2021-03-11T14:17:59","modified_gmt":"2021-03-11T08:47:59","slug":"sql-wildcard","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/","title":{"rendered":"SQL Wildcard Characters With Example"},"content":{"rendered":"

In this tutorial, we will focus on the SQL wildcards and discuss it in detail. We will study each available wildcard in detail and then discuss the examples to clear our concepts.<\/p>\n

What are Wildcards in SQL?<\/h3>\n

SQL wildcards are put to substitute one or more characters in a string. Wildcards come in handy when we need to compare the strings and also aim to get the minute details.<\/p>\n

Wildcards are put up with the LIKE operator and come in handy for solving complex queries.<\/p>\n

For example, let us imagine a condition where we have a large amount of data available. And we want to find out details of all the people who lie in the age group 20 to 30 whose names start from A or N.<\/p>\n

For resolving such complex queries we use wildcards.<\/p>\n

Some of the most commonly used wildcards are as follows:<\/p>\n

1. Wildcards in MS Access<\/h3>\n\n\n\n\n\n\n\n\n\n
Sr.No<\/b><\/td>\nRepresentation Symbol<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
1<\/b><\/td>\n*<\/b><\/td>\nStands for zero or more characters.<\/span><\/td>\n<\/tr>\n
2<\/b><\/td>\n?<\/b><\/td>\nStands for a single character.<\/span><\/td>\n<\/tr>\n
3<\/b><\/td>\n[ ]<\/b><\/td>\nStands for a single character within the brackets.<\/span><\/td>\n<\/tr>\n
4<\/b><\/td>\n!<\/b><\/td>\nStands for the characters not available in the bracket.<\/span><\/td>\n<\/tr>\n
5<\/b><\/td>\n–<\/b><\/td>\nStands for a range of characters.<\/span><\/td>\n<\/tr>\n
6<\/b><\/td>\n#<\/b><\/td>\nStands for a single numeric character.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

2. Wildcards in SQL Server<\/h3>\n\n\n\n\n\n\n\n\n
Sr.No<\/b><\/td>\nRepresentation Symbol<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
1<\/b><\/td>\n%<\/b><\/td>\nStands for zero or more characters.<\/span><\/td>\n<\/tr>\n
2<\/b><\/td>\n_<\/b><\/td>\nStands for a single character.<\/span><\/td>\n<\/tr>\n
3<\/b><\/td>\n[ ]<\/b><\/td>\nStands for a single character specific to the brackets.<\/span><\/td>\n<\/tr>\n
4<\/b><\/td>\n^<\/b><\/td>\nStands for any character not in brackets.<\/span><\/td>\n<\/tr>\n
5<\/b><\/td>\n–<\/b><\/td>\nStands for a range of characters.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Why do we Need Wildcards?<\/h3>\n

We use SQL wildcards when we need to search for complex data. This complex data could compromise strings or numerical with special characteristics.<\/p>\n

Wildcards also come in handy when we want to speed up our querying process. The results are considerably fast when wildcards are in use.<\/p>\n

We have already seen SQL Wildcards are put up with the LIKE operator.<\/p>\n

Let us consider the usage and then we would discuss the examples:<\/p>\n\n\n\n\n\n\n\n\n\n
Sr.No<\/b><\/td>\nUsage with LIKE operator<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
1<\/b><\/td>\nWHERE columnName LIKE \u2018a%\u2019<\/span><\/td>\nReturns values which start with \u2018a\u2019.<\/span><\/td>\n<\/tr>\n
2<\/b><\/td>\nWHERE columnName LIKE \u2018%a\u2019<\/span><\/td>\nReturns values which end with \u2018a\u2019.<\/span><\/td>\n<\/tr>\n
3<\/b><\/td>\nWHERE columnName LIKE \u2018%ab%\u2019<\/span><\/td>\nReturns any value that has \u2018ab\u2019 in any position.<\/span><\/td>\n<\/tr>\n
4<\/b><\/td>\nWHERE columnName LIKE \u2018_a%\u2019<\/span><\/td>\nReturns any value which has \u2018a\u2019 as the second character.<\/span><\/td>\n<\/tr>\n
5<\/b><\/td>\nWHERE columnName LIKE \u2018a_%_%\u2019<\/span><\/td>\nReturns any value that starts with \u2018a\u2019 and is at least 3 characters in length.<\/span><\/td>\n<\/tr>\n
6<\/b><\/td>\nWHERE columnName LIKE \u2018a%b\u2019<\/span><\/td>\nReturns any value which starts with \u2018a\u2019 and ends with \u2018b\u2019.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Demo Database<\/h3>\n

Let us understand the database we will be using in this tutorial.
\nQuery:\u00a0<\/strong><\/p>\n

SELECT * FROM DataFlair_emp2;\r\n<\/pre>\n
\"demo<\/a><\/p>\n
Here we can see the contents of our demo database.<\/p>\n
Example 1:<\/strong> Let us find out the name of employees starting with \u2018A\u2019.
\nQuery:<\/strong><\/p>\n
SELECT * FROM dataflair_emp2\r\nWHERE name_emp LIKE 'A%';\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Here we can see the names of employees who have \u2018A\u2019 as the first character in the name.<\/p>\n
Example 2:<\/strong> Let us find the employees whose locations contain \u2018nd\u2019.
\nQuery:<\/strong><\/p>\n
SELECT * FROM dataflair_emp2\r\nWHERE location LIKE '%nd%';\r\n<\/pre>\n
\"Wildcard<\/a><\/p>\n
We can observe the locations with the required pattern.<\/p>\n
Example 3:<\/strong> Let us find the employee with three characters in his or her name.
\nQuery:<\/strong><\/p>\n
SELECT * FROM dataflair_emp2\r\nWHERE name_emp LIKE '___';\r\n<\/pre>\n
\"Example<\/a><\/p>\n
Here we can see the details of employees with three characters in the name field.<\/p>\n
Example 4:<\/strong> Let us now find the cities which end with \u2018e\u2019 in our database.
\nQuery:<\/strong><\/p>\n
SELECT location as City FROM dataflair_emp2\r\nWHERE location LIKE '%e';\r\n<\/pre>\n
\"wildcard<\/a><\/p>\n
Here we can see the details of the required locations according to the filter.<\/p>\n
Example 5:<\/strong> Let us find the details of locations that have \u2018u\u2019 as the second character.
\nQuery:<\/strong><\/p>\n
 SELECT location as City FROM dataflair_emp2\r\nWHERE location LIKE '_u%';\r\n<\/pre>\n
\"wildcard<\/a><\/p>\n
Here we can see the results of locations that have \u2018u\u2019 as the second character.<\/p>\n
Example 6:<\/strong> Let us find the details of locations with the first character as \u2018P\u2019 and the last character as \u2018e\u2019.
\nQuery:<\/strong><\/p>\n
SELECT * FROM dataflair_emp2\r\nWHERE location LIKE 'p%e';\r\n<\/pre>\n
\"Example<\/a><\/p>\n
Here we can see the details of locations starting with \u2018p\u2019 and ending with \u2018e\u2019.<\/p>\n
Summary<\/h3>\n
In this tutorial, we have discussed and understood how SQL wildcards are put and how they are beneficial. We have discussed various examples.<\/p>\n
We have also understood how we can use Wildcards to resolve complex queries.<\/p>\n
When we use wildcards, we need to take care to use the LIKE operator as the wildcards are functional only with the LIKE operator.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this tutorial, we will focus on the SQL wildcards and discuss it in detail. We will study each available wildcard in detail and then discuss the examples to clear our concepts. What are...<\/p>\n","protected":false},"author":6,"featured_media":86893,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[4358,13591,13592,16214],"class_list":["post-23315","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-example-of-sql-wildcard","tag-sql-wildcard-example","tag-sql-wildcard-syntax","tag-wildcards-in-sql"],"yoast_head":"\nSQL Wildcard Characters With Example - DataFlair<\/title>\n<meta name=\"description\" content=\"SQL Wildcard,Wildcards in SQL,SQL Wildcard Characters,SQL Wildcard example,what is SQL Wildcard,synatx of SQL Wildcard,% Wildcard,_ Wildcard\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-wildcard\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Wildcard Characters With Example - DataFlair\" \/>\n<meta property=\"og:description\" content=\"SQL Wildcard,Wildcards in SQL,SQL Wildcard Characters,SQL Wildcard example,what is SQL Wildcard,synatx of SQL Wildcard,% Wildcard,_ Wildcard\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-wildcard\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-13T07:05:06+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-11T08:47:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Wildcards.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Wildcard Characters With Example - DataFlair","description":"SQL Wildcard,Wildcards in SQL,SQL Wildcard Characters,SQL Wildcard example,what is SQL Wildcard,synatx of SQL Wildcard,% Wildcard,_ Wildcard","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/","og_locale":"en_US","og_type":"article","og_title":"SQL Wildcard Characters With Example - DataFlair","og_description":"SQL Wildcard,Wildcards in SQL,SQL Wildcard Characters,SQL Wildcard example,what is SQL Wildcard,synatx of SQL Wildcard,% Wildcard,_ Wildcard","og_url":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-08-13T07:05:06+00:00","article_modified_time":"2021-03-11T08:47:59+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Wildcards.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Wildcard Characters With Example","datePublished":"2018-08-13T07:05:06+00:00","dateModified":"2021-03-11T08:47:59+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/"},"wordCount":670,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Wildcards.jpg","keywords":["Example of SQL Wildcard","SQL Wildcard Example","SQL Wildcard syntax","Wildcards in SQL"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-wildcard\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/","url":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/","name":"SQL Wildcard Characters With Example - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Wildcards.jpg","datePublished":"2018-08-13T07:05:06+00:00","dateModified":"2021-03-11T08:47:59+00:00","description":"SQL Wildcard,Wildcards in SQL,SQL Wildcard Characters,SQL Wildcard example,what is SQL Wildcard,synatx of SQL Wildcard,% Wildcard,_ Wildcard","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-wildcard\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Wildcards.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Wildcards.jpg","width":1200,"height":628,"caption":"SQL Wildcards"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-wildcard\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Searching for Groups of Scala Regex","item":"https:\/\/data-flair.training\/blogs\/tag\/searching-for-groups-of-scala-regex\/"},{"@type":"ListItem","position":3,"name":"SQL Wildcard Characters With Example"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/23315","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=23315"}],"version-history":[{"count":6,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/23315\/revisions"}],"predecessor-version":[{"id":86901,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/23315\/revisions\/86901"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86893"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=23315"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=23315"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=23315"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}