SQL substring between two characters

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 where we need to use a similar example of C# Truncate String Extension. 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.

Published on Jul 3, 2022

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