{"id":20923,"date":"2020-08-07T01:27:29","date_gmt":"2020-08-07T08:27:29","guid":{"rendered":"https:\/\/careerkarma.com\/blog\/?p=20923"},"modified":"2021-01-04T05:51:28","modified_gmt":"2021-01-04T13:51:28","slug":"sql-substring","status":"publish","type":"post","link":"https:\/\/careerkarma.com\/blog\/sql-substring\/","title":{"rendered":"SQL Substr() Function"},"content":{"rendered":"\n<p>Sometimes we need just a portion of a string value in our data to display. There is a string function we use called <code>SUBSTR()<\/code>. There is also an equivalent function called <code>SUBSTRING()<\/code>. In this article, we cover using this substring function in SQL.&nbsp;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Preparation<\/h2>\n\n\n\n<p>Here is the <a href=\"http:\/\/sqlfiddle.com\/#!9\/6ef87e\/13\/0\" target=\"_blank\" rel=\"noopener\" rel=\"nofollow\">SQL Fiddle<\/a> used in this article. The schema is as follows:<br><\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre>create table names (\n   id INT,\n   name VARCHAR(50),\n   age INT,\n   gender VARCHAR(50)\n);\ninsert into names (id, name, age, gender) values (1, 'Bret Starkings', 55, 'M');\ninsert into names (id, name, age, gender) values (2, 'Bobbye Eyckel', 76, 'F');\ninsert into names (id, name, age, gender) values (3, 'Barbie Veschi', 50, 'F');\ninsert into names (id, name, age, gender) values (4, 'Electra Blazewicz', 47, 'F');<\/pre><\/div>\n\n\n\n<p>We use the <code>SUBSTR()<\/code> function to get back a portion of the name column. The syntax of the function is as follows:&nbsp;<br><\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre>SUBSTR(column_name, position, num_letters);<\/pre><\/div>\n\n\n\n<p>The function takes three parameters: the column or string we want to extrapolate the substring from, a one-based start position in the string (indexing that starts at 1 instead of 0), and the number of character length. If we do this on the name column, the SQL Statement comes out as:<br><\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre>SELECT name, SUBSTR(name, 1, 4) AS NAME_SUBSTRING, age, gender\nFROM names;<\/pre><\/div>\n\n\n\n<p>And the code results in:<br><\/p>\n\n\n\n<table class=\"wp-block-table course-info-table\"><tbody><tr><td><strong>name<\/strong><\/td><td><strong>NAME_SUBSTRING<\/strong><\/td><td><strong>age<\/strong><\/td><td><strong>gender<\/strong><\/td><\/tr><tr><td>Bret Starkings<\/td><td>Bret<\/td><td>55<\/td><td>M<\/td><\/tr><tr><td>Bobbye Eyckel<\/td><td>Bobb<\/td><td>76<\/td><td>F<\/td><\/tr><tr><td>Barbie Veschi<\/td><td>Barb<\/td><td>50<\/td><td>F<\/td><\/tr><tr><td>Electra Blazewicz<\/td><td>Elec<\/td><td>47<\/td><td>F<\/td><\/tr><tr><td>Estrella Borleace<\/td><td>Estr<\/td><td>57<\/td><td>F<\/td><\/tr><\/tbody><\/table>\n\n\n\n<p><\/p>\n\n\n\n<p>The third parameter in this function can be left off if you choose:<br><\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre>SELECT name, SUBSTR(name, 4) AS NAME_SUBSTRING, age, gender\nFROM names;<\/pre><\/div>\n\n\n\n<table class=\"wp-block-table course-info-table\"><tbody><tr><td><strong>name<\/strong><\/td><td><strong>NAME_SUBSTRING<\/strong><\/td><td><strong>age<\/strong><\/td><td><strong>gender<\/strong><\/td><\/tr><tr><td>Bret Starkings<\/td><td>t Starkings<\/td><td>55<\/td><td>M<\/td><\/tr><tr><td>Bobbye Eyckel<\/td><td>bye Eyckel<\/td><td>76<\/td><td>F<\/td><\/tr><tr><td>Barbie Veschi<\/td><td>bie Veschi<\/td><td>50<\/td><td>F<\/td><\/tr><tr><td>Electra Blazewicz<\/td><td>ctra Blazewicz<\/td><td>47<\/td><td>F<\/td><\/tr><tr><td>Estrella Borleace<\/td><td>rella Borleace<\/td><td>57<\/td><td>F<\/td><\/tr><\/tbody><\/table>\n\n\n\n<p><\/p>\n\n\n\n<p>You can also use negative values for the position:<br><\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre>SELECT name, SUBSTR(name, -4) AS NAME_SUBSTRING, age, gender\nFROM names;<\/pre><\/div>\n\n\n\n<table class=\"wp-block-table course-info-table\"><tbody><tr><td><strong>name<\/strong><\/td><td><strong>NAME_SUBSTRING<\/strong><\/td><td><strong>age<\/strong><\/td><td><strong>gender<\/strong><\/td><\/tr><tr><td>Bret Starkings<\/td><td>ings<\/td><td>55<\/td><td>M<\/td><\/tr><tr><td>Bobbye Eyckel<\/td><td>ckel<\/td><td>76<\/td><td>F<\/td><\/tr><tr><td>Barbie Veschi<\/td><td>schi<\/td><td>50<\/td><td>F<\/td><\/tr><tr><td>Electra Blazewicz<\/td><td>wicz<\/td><td>47<\/td><td>F<\/td><\/tr><tr><td>Estrella Borleace<\/td><td>eace<\/td><td>57<\/td><td>F<\/td><\/tr><\/tbody><\/table>\n\n\n\n<p><\/p>\n\n\n\n<p>As with any of these SQL functions, syntax might be slightly different depending on the type of database you are using. The general concept is the same though. Just remember the indexing starts on 1 instead of 0 for the position parameter and you\u2019ll do great at building your queries.<\/p>\n","protected":false},"excerpt":{"rendered":"Sometimes we need just a portion of a string value in our data to display. There is a string function we use called SUBSTR(). There is also an equivalent function called SUBSTRING(). In this article, we cover using this substring function in SQL.&nbsp; Preparation Here is the SQL Fiddle used in this article. The schema&hellip;","protected":false},"author":77,"featured_media":20924,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[17284],"tags":[],"class_list":{"0":"post-20923","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-sql"},"acf":{"post_sub_title":"","sprint_id":"","query_class":"SQL","school_sft":"","parent_sft":"","school_privacy_policy":"","has_review":null,"is_sponser_post":"","is_guest_post":""},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.0 (Yoast SEO v27.0) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Substr() Function: A Complete Guide | Career Karma<\/title>\n<meta name=\"description\" content=\"The SQL Substr() function can be used in a query to return only a portion of a string in any given column. Learn how to use it here.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/careerkarma.com\/blog\/sql-substring\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Substr() Function\" \/>\n<meta property=\"og:description\" content=\"The SQL Substr() function can be used in a query to return only a portion of a string in any given column. Learn how to use it here.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/careerkarma.com\/blog\/sql-substring\/\" \/>\n<meta property=\"og:site_name\" content=\"Career Karma\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/facebook.com\/careerkarmaapp\" \/>\n<meta property=\"article:published_time\" content=\"2020-08-07T08:27:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-04T13:51:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1020\" \/>\n\t<meta property=\"og:image:height\" content=\"678\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Christina Kopecky\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@career_karma\" \/>\n<meta name=\"twitter:site\" content=\"@career_karma\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Christina Kopecky\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/\"},\"author\":{\"name\":\"Christina Kopecky\",\"@id\":\"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/ae0cdc4a5d198690d78482646894074e\"},\"headline\":\"SQL Substr() Function\",\"datePublished\":\"2020-08-07T08:27:29+00:00\",\"dateModified\":\"2021-01-04T13:51:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/\"},\"wordCount\":287,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg\",\"articleSection\":[\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/careerkarma.com\/blog\/sql-substring\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/\",\"url\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/\",\"name\":\"SQL Substr() Function: A Complete Guide | Career Karma\",\"isPartOf\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg\",\"datePublished\":\"2020-08-07T08:27:29+00:00\",\"dateModified\":\"2021-01-04T13:51:28+00:00\",\"author\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/ae0cdc4a5d198690d78482646894074e\"},\"description\":\"The SQL Substr() function can be used in a query to return only a portion of a string in any given column. Learn how to use it here.\",\"breadcrumb\":{\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/careerkarma.com\/blog\/sql-substring\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage\",\"url\":\"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg\",\"contentUrl\":\"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg\",\"width\":1020,\"height\":678,\"caption\":\"Pink, Orange, Yellow, Green, Blue, and Violet String\/Thread\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/careerkarma.com\/blog\/sql-substring\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Blog\",\"item\":\"https:\/\/careerkarma.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL\",\"item\":\"https:\/\/careerkarma.com\/blog\/sql\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Substr() Function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/careerkarma.com\/blog\/#website\",\"url\":\"https:\/\/careerkarma.com\/blog\/\",\"name\":\"Career Karma\",\"description\":\"Latest Coding Bootcamp News &amp; Career Hacks from Industry Insiders\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/careerkarma.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/ae0cdc4a5d198690d78482646894074e\",\"name\":\"Christina Kopecky\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/06\/image-3-150x150.jpg\",\"contentUrl\":\"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/06\/image-3-150x150.jpg\",\"caption\":\"Christina Kopecky\"},\"description\":\"Christina is an experienced technical writer, covering topics as diverse as Java, SQL, Python, and web development. She earned her Master of Music in flute performance from the University of Kansas and a bachelor's degree in music with minors in French and mass communication from Southeast Missouri State. Prior to joining the Career Karma team in June 2020, Christina was a teaching assistant, team lead, and section lead at Lambda School, where she led student groups, performed code and project reviews, and debugged problems for students. Christina's technical content is featured frequently in publications like Codecademy, Repl.it, and Educative.\",\"sameAs\":[\"http:\/\/www.linkedin.com\/in\/cmvnk\"],\"url\":\"https:\/\/careerkarma.com\/blog\/author\/christina-kopecky\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Substr() Function: A Complete Guide | Career Karma","description":"The SQL Substr() function can be used in a query to return only a portion of a string in any given column. Learn how to use it here.","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:\/\/careerkarma.com\/blog\/sql-substring\/","og_locale":"en_US","og_type":"article","og_title":"SQL Substr() Function","og_description":"The SQL Substr() function can be used in a query to return only a portion of a string in any given column. Learn how to use it here.","og_url":"https:\/\/careerkarma.com\/blog\/sql-substring\/","og_site_name":"Career Karma","article_publisher":"http:\/\/facebook.com\/careerkarmaapp","article_published_time":"2020-08-07T08:27:29+00:00","article_modified_time":"2021-01-04T13:51:28+00:00","og_image":[{"width":1020,"height":678,"url":"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg","type":"image\/jpeg"}],"author":"Christina Kopecky","twitter_card":"summary_large_image","twitter_creator":"@career_karma","twitter_site":"@career_karma","twitter_misc":{"Written by":"Christina Kopecky","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/#article","isPartOf":{"@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/"},"author":{"name":"Christina Kopecky","@id":"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/ae0cdc4a5d198690d78482646894074e"},"headline":"SQL Substr() Function","datePublished":"2020-08-07T08:27:29+00:00","dateModified":"2021-01-04T13:51:28+00:00","mainEntityOfPage":{"@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/"},"wordCount":287,"commentCount":0,"image":{"@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage"},"thumbnailUrl":"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg","articleSection":["SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/careerkarma.com\/blog\/sql-substring\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/","url":"https:\/\/careerkarma.com\/blog\/sql-substring\/","name":"SQL Substr() Function: A Complete Guide | Career Karma","isPartOf":{"@id":"https:\/\/careerkarma.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage"},"image":{"@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage"},"thumbnailUrl":"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg","datePublished":"2020-08-07T08:27:29+00:00","dateModified":"2021-01-04T13:51:28+00:00","author":{"@id":"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/ae0cdc4a5d198690d78482646894074e"},"description":"The SQL Substr() function can be used in a query to return only a portion of a string in any given column. Learn how to use it here.","breadcrumb":{"@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/careerkarma.com\/blog\/sql-substring\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/#primaryimage","url":"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg","contentUrl":"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/08\/mel-poole-4J1sjmMr0Ro-unsplash.jpg","width":1020,"height":678,"caption":"Pink, Orange, Yellow, Green, Blue, and Violet String\/Thread"},{"@type":"BreadcrumbList","@id":"https:\/\/careerkarma.com\/blog\/sql-substring\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog","item":"https:\/\/careerkarma.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL","item":"https:\/\/careerkarma.com\/blog\/sql\/"},{"@type":"ListItem","position":3,"name":"SQL Substr() Function"}]},{"@type":"WebSite","@id":"https:\/\/careerkarma.com\/blog\/#website","url":"https:\/\/careerkarma.com\/blog\/","name":"Career Karma","description":"Latest Coding Bootcamp News &amp; Career Hacks from Industry Insiders","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/careerkarma.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/ae0cdc4a5d198690d78482646894074e","name":"Christina Kopecky","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/careerkarma.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/06\/image-3-150x150.jpg","contentUrl":"https:\/\/careerkarma.com\/blog\/wp-content\/uploads\/2020\/06\/image-3-150x150.jpg","caption":"Christina Kopecky"},"description":"Christina is an experienced technical writer, covering topics as diverse as Java, SQL, Python, and web development. She earned her Master of Music in flute performance from the University of Kansas and a bachelor's degree in music with minors in French and mass communication from Southeast Missouri State. Prior to joining the Career Karma team in June 2020, Christina was a teaching assistant, team lead, and section lead at Lambda School, where she led student groups, performed code and project reviews, and debugged problems for students. Christina's technical content is featured frequently in publications like Codecademy, Repl.it, and Educative.","sameAs":["http:\/\/www.linkedin.com\/in\/cmvnk"],"url":"https:\/\/careerkarma.com\/blog\/author\/christina-kopecky\/"}]}},"_links":{"self":[{"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/posts\/20923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/users\/77"}],"replies":[{"embeddable":true,"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/comments?post=20923"}],"version-history":[{"count":0,"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/posts\/20923\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/media\/20924"}],"wp:attachment":[{"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/media?parent=20923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/categories?post=20923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/careerkarma.com\/blog\/wp-json\/wp\/v2\/tags?post=20923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}