How to extract text between square brackets in TSQL

Question

I have the following query:

DECLARE @value as nvarchar(max)
SET @value   = '(company.[department] LIKE ''Development'')';

I would like to extract the word between brackets keep it in a value and then put as input in a replace function like this.

select replace(@value, @department, 'another_string');

You will say probably why I don't do it immediately with the replace function. The case is that this department string may change dynamically to another string for example country and I would like every time to keep this choice and change it with a value.

Solution 1

your particular case will idealy works with parsenamefunction:

DECLARE @value as nvarchar(max), @department varchar(100);
SET @value = '(company.[department] LIKE ''Development'')';
SET @department = parsename(replace(replace(@value,'[','.'),']','.'),2)

SELECT replace(@value, @department, 'another_string');

will return:

(company.[another_string] LIKE 'Development')

---

explanation:

replace brackets with dot '.' and your @value will looks like this:

(company..department. LIKE 'Development')

such pattern is similar with:

Server name.Database name.Schema name.Object name

and you can extract the part of string using parsename function, where: 1 = Object name, 2 = Schema name, 3 = Database name, 4 = Server name

link to function here: https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql

DEMO

Solution 2

You can do this in a query via the base string functions:

SELECT
    SUBSTRING(col,
              CHARINDEX('[', col) + 1,
              CHARINDEX(']', col) - CHARINDEX('[', col) - 1) AS output
FROM yourTable;

Caveats include that you only have one bracketed term, and also that this query form of an answer would be usable in your particular scenario.

Demo

Solution 3

Try like following.

DECLARE @value as nvarchar(max) 
SET @value = '(company.[department] LIKE ''Development'')'; 
declare @department NVARCHAR(100) 
SELECT @department = Substring(@value, 
                       Charindex('[', @value) + 1, 
                       Charindex(']', @value) - 
                       Charindex('[', @value) - 1) 

select Replace(@value, @department, 'another_string'); 

Output

(company.[another_string] LIKE 'Development')