Follow @endyourif rss Facebook LinkedIn

SQL substring between two characters SQL substring between two characters

Published on Jul 3, 2022

I'm starting to feel like a broken record when I say one of my favorite things to do is write functions that will leverage the SQL server substring to select from the starting position of your first character, string, or delimeter aka your input string. I've previously written a String Extract Extension Method with C# and of course I've also written a StringExtractComponent for CakePHP in case you're interested in something similar in different programming languages.

Table of contents:

  1. SQL server function to select substring between two words or delimeters
  2. Using CHARINDEX function to get the input string
  3. Using the SQL server substring function to extract characters between the starting position and ending position
  4. Create table value function to extract characters
  5. Using the table value function to extract characters using SQL substring function
  6. How do I extract a string between two characters?
  7. How do you get a specific substring from a string in SQL?
  8. Can Between be used for characters in SQL?
  9. What is Substr in SQL?
  10. How do I substring a string in SQL Server?
  11. Can we use substring in SQL?
  12. What is substring in SQL Server?
  13. How do I get last 4 characters of a string in SQL?
  14. How do I get the first 10 characters of a string in SQL?
  15. How do I get the first 5 characters of a string in SQL?

SQL server function to select substring between two words or delimeters

Let's dive right in where I will leverage a combination of CHARINDEX function, SUBSTRING function, LEN, and LTRIM and RTRIM to ensure we don't get unnecessary space character data. The final solution will create a re-usable table value function (TVF) that will be easy to implement in the following query as it returns just the extracted characters between the text data leveraging the ms sql substring.

Using CHARINDEX function to get the input string

The first thing we need to do is use the charindex to get the starting position to extract characters using the following syntax. This will be the first of the several different substring functions that I will leverage to SQL server to get the substring between two characters with sqlserver substring.

In the following examples I will declare three different variables to use with substring in sql:

  1. @StringToSearch - This will contain the original string aka the source string
  2. @StartString - This will be used to find the starting position of that the function extracts
  3. @EndString - This will be used in the substring syntax that specifies the ending position of the second character

DECLARE @Start int = CHARINDEX(@StartString, @StringToSearch)
DECLARE @End int = CHARINDEX(@EndString, @StringToSearch, @Start)

Before proceeding we need to ensure the start position length is greater than 0 as well as the index of the end position is also a positive integer.

IF @Start > 0 AND @End > 0
BEGIN
	SET @Start += LEN(@StartString)
	-- Code to extract substring goes here next
END

When the substring expression has values I add the length of the substring so it doesn't include the number of characters in the original string and instead uses the total length in t sql.

Using the SQL server substring function to extract characters between the starting position and ending position

Now that we know how many characters are between the specified length of the start and end of the substring we want to extra the literal strings with the following query that we can use with substring between two characters c#.

DECLARE @ExtractedData NVARCHAR(MAX)
SET @ExtractedData = LTRIM(RTRIM(SUBSTRING(@StringToSearch, @Start, @End - @Start)))

This should replace the previous code comment inside our where clause to ensure we get only the digits using the SQL server substring function. If you need to convert to a specific data types then you can use the SQL server CONVERT function.

Create table value function to extract characters

Let's complete our SQL server example to select substring using the starting position with the number of characters between the start and end substring. In case you're not familiar with a table value function (TVF) I've linked an excellent explanation of it.

IF EXISTS (SELECT 1 FROM sys.all_objects WHERE [Name] = 'ExtractData' AND [type_desc] = 'SQL_TABLE_VALUED_FUNCTION')
DROP FUNCTION [dbo].[ExtractData]
GO
CREATE FUNCTION [dbo].[ExtractData]
(
    @StringToSearch NVARCHAR(max),
	@StartString NVARCHAR(255),
	@EndString NVARCHAR(255)
)  
RETURNS @RtnValue TABLE
(
	ExtractedData NVARCHAR(MAX)
) 
AS  
BEGIN 
	DECLARE @ExtractedData NVARCHAR(MAX)
	DECLARE @Start int = CHARINDEX(@StartString, @StringToSearch)
	DECLARE @End int = CHARINDEX(@EndString, @StringToSearch, @Start)
	IF @Start > 0 AND @End > 0
	BEGIN
		SET @Start += LEN(@StartString)
		SET @ExtractedData = LTRIM(RTRIM(SUBSTRING(@StringToSearch, @Start, @End - @Start)))
	END
	INSERT INTO @RtnValue VALUES (@ExtractedData)
	RETURN
END
GO

Using the table value function to extract characters using SQL substring function

Now that we have our TVF function let's use this select statement in the following example on a new table example. If you're looking for more practical examples I recommend exploring more about the CASE Statement in SQL. With your table columns defined that should have a primary key we can use our function to get the character binary text in sql server.

SELECT ExtractData.ExtractedData
FROM MyTable
OUTER APPLY ExtractData(MyTable.StringDataToExtractFrom, '<a href="', '"</a>') as ExtractData

This example uses our SQL server ExtractData function to get the substring between two characters. The final solution doesn't even need to use the left function or the right function and the query returns exactly what we want from the where clause.

In this example, assuming the string contains an a tag it will extract characters only returning you the URL with the following query.

Before wrapping up, I'll try to answer some common questions about the syntax substring with our character binary text.

How do I extract a string between two characters?

For the extraction of parts from two different characters you can do this: Select one of the cells where you want the results, Type the following formulas: MID(LEFT( A1, FIND( ">“A1)-),FIND(” -A1). Note that A1 is text and, the two letters that the string consists of.

How do you get a specific substring from a string in SQL?

Using the SUBSTRING() Function. The first arguments are the strings and column names. The second argument indicates an index for a character starting with a Substring. Three arguments are length of substring.

Can Between be used for characters in SQL?

If we've used an operator with two characters, this returns the entire database where a column names a letter between the two characters. If we are using NOT WITH BETWEEN operator, the system returns every record with columns where the name of each field is not a starting point.

What is Substr in SQL?

The function substrings returns a part of the character starting at character position, a substring length character longer. The length of SUSTR's input characters is calculated according to input characters sets. SubString uses bytes rather than characters.

How do I substring a string in SQL Server?

SQL Server SUBSTRACT Functions. Get the 3 character string, from 1 position: SELECT SUBSTRING('SQL tutorials'1, 3) as the ExtractString. Extract five lines from the “CustomersName”, beginning with position 1: Delete 100 characters in the string.

Can we use substring in SQL?

Substring() is an SQL function that allows users to derive substrings from string sets based on user's requirements. Substring() rewrites a string with a specified length beginning in the input. Substring() uses SQL to retrieve specific portions of strings.

What is substring in SQL Server?

Substring() is a SQL function that allows users to extract substrings from string sets depending on their need. Substring() takes a string of specified length from any specified location in the input string.

How do I get last 4 characters of a string in SQL?

Use LEFT() for the first n character strings in MySQL. In SQLite you can use right_() to retrieve string nchars.

How do I get the first 10 characters of a string in SQL?

SQL Server LEFT() Function. The LEFT() function removes a string (beginning from left) from the string.

How do I get the first 5 characters of a string in SQL?

How can one retrieve the five characters in sql? – Substtrs. (strings start, [ length ]) SELECT substr(‘Hi World'). Choose a subst. Seeks Substrat( ‘Hey worlds', 4); CHOOSE substr(‘Hey World', -3); – Rld.

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | Database | charindex | substring

About Jamie Munro

Welcome to my blog. I have been compiling articles since 2009! Below is a list of every post I've ever created. Some of my major focuses have been compiling the best source of jQuery tutorials on the Internet today. A recent focus of mine is now focusing on creating the best SQL Tutorials for Beginners, Intermediate and Advanced Users. This is a compiliation of all my experience about how to do common SQL commands to optimize for performance. As you might see from the links around here, I've also written and published 5 books. My first and last book are focused around ASP.NET MVC and Web API tutorials. The last book has a big focus on integrating Knockout.js as the MVVM for the front-end code. O'Reilly Media was so interested in Knockout.js that they actually commissioned me to write that book first as it is one of the only books dedicated entirely to this MVVM library. So if you're looking for the best Knockout.js tutorials, I've compiled the best list. My first book, even though it was self published, was on the PHP framework: CakePHP. I used this framework for years and many of my first ever exampes were all focused on CakePHP tutorials that provide so many fantastic PHP examples. There are so many Javascript tutorials out there, but I noticed that one of the things I work on most with Javascript is arrays. With this I've worked hard on compiling a big list of really advanced Javascript array examples. These array examples are amazing including how to group by, get distinct array elements and so many more. I've also dabbled a little bit with Node.js and during those experiments I definitely ran into some common errors like Can't set headers after they are sent, Solving No Access-Control-Allow-Origin with Node js and Express, and Uncaught ReferenceError: require is not defined. With these common errors I've compiled a list of Node.js tutorials that help solve these problems, but more importantly getting you started with the basics.