Semi-join in SQL Server

This is very interesting and good to know that Correlated sub queries is called as Semi-join in SQL Server.

Semi-Join returns rows from a table that would join with another table without performing a complete join.

Ex:

SELECT a.FirstName, a.LastName

FROM Person.Person AS a

WHERE EXISTS

(SELECT *

    FROM HumanResources.Employee AS b

    WHERE a.BusinessEntityID = b.BusinessEntityID

    AND a.LastName = 'Pritam');

GO

Stuff number inside square brackets (Ex: [123]) from a string in SQL Server

When you Search square brackets from a string... you can't get it if you write query
like '%[ ]%'

Here my goal is to remove the number with square brackets but not the character  inside square brackets.

To achieve this goal you have to follow the code given below.

DECLARE @TEXT VARCHAR(MAX),@INITIAL INT,@FINAL INT,@PATSTART 

INT,@PATEND INT;

SET @TEXT='ASFASFA [123] ASF [ABC] AFAFS [567] 123 AFASFA [123] AFASF PRITAM[12]DAS'

SET @INITIAL=1;

SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT)

WHILE (@INITIAL>0)

BEGIN

       SET @PATSTART=@INITIAL-1;

       SET @PATEND=CHARINDEX(']',@TEXT,@INITIAL);

       SET @TEXT=STUFF(@TEXT,@PATSTART,@PATEND+1-@PATSTART,'');

       SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT);

END

SELECT @TEXT;