Extract String after a character or before character in SQL Server

: 4040

Last Updated : 14/11/2022

Posted By :- vikas_jk

In SQL Server, we have functions like SubString, CharIndex etc using which we can extract string or substring before or after character in SQL Server, so in this article, I have mentioned how we can do it using various examples.

Get substring after a character in SQL Server

In this case, we will check the query, when we want to extract a substring before a character in a string using SQL Server query, so for example, we have the below string

abcd-12345-6789

Now, we need to extract the string after the last "-", then we can have query like below

select right('abcd-12345-6789', charindex('-', reverse('abcd-12345-6789')) - 1)

Here is the sample, output when we run above query in SSMS

extract-string-after-character-sql-server

Get Substring after a character in SQL Server

We can also reverse the case here, if you want only characters, before the first "-" character in string, then you can simply use below query

select left('abcd-12345-6789', charindex('-', reverse('abcd-12345-6789')) - 1)

Output:

abcd

Using SubString in SQL Server

In the above example, we are using CharIndex function with Left/right function, but instead of using Left/Right, we can also use SubString() in SQL Server

SubString has following Syntax

SUBSTRING(string, start, length)

Where string = The string to extract from, start = starting position to extract the substring and length = how many characters of the expression will be returned.

So considering above examples, we have query to extract sub-string before or after a character in SQL Server using SubString function, can be as below

----select characters before '-' 
select SUBSTRING ('abcd-12345-6789',0,CHARINDEX('-','abcd-12345-6789'))

--select characters after '-' 
select SUBSTRING('abcd-12345-6789',CHARINDEX('-','abcd-12345-6789') +1,LEN('abcd-12345-6789'))

Output: