SELECT 
      A.*,
      CASE 
          WHEN B.YourColumn LIKE 'some text needed%' 
          THEN STUFF(B.YourColumn, 1, CHARINDEX(',', B.YourColumn + ','), '')
          ELSE REPLACE(REPLACE(B.YourColumn, '[', ''), ']', '')
      END as manipulated_column
    FROM 
      TableA AS A
    JOIN 
      TableB AS B 
      ON A.JoinColumn = B.JoinColumn

• STUFF() : This function is used to remove a part of the string and replace it with another string.
• CHARINDEX() : This finds the index of the first occurrence of a string within another one.
• REPLACE() : This is used twice to remove both the '[' and ']'

Here's how I'd go about it. I've created my own dataset here since none was provided in the question, and got rid of the joins and expressions that are immaterial to the question. Ostensibly, your question is about formatting the strings. So I'll start with this data:

drop table if exists #data
select String
into #data
from
(
    values
    ('[some text needed]'),
    ('[some text needed, other]'),
    ('[some text needed, extra text]'),
    ('[some text needed, extra - text, required]'),
    ('[today is a rainy day]'),
    ('[Extra hot]')
) a (string)

Again, I think the square brackets question is probably immaterial to the question since you even said it's not causing you any problems. But I'll leave them in since you mentioned it.

The first thing I'd do is strip off the square brackets. You can do that with the trim function and providing a list of characters to trim. In this case, I've chosen to trim [] to get rid of the brackets and any whitespace that might remain.

First, I'm going to assign that special value "some text needed" to a variable so I don't have to re-type it everyone.

Then you just have to handle your different cases. Since you can have a string which is just "Some text needed" with no item after it, let's treat that as a special case, and set its output value to an empty string.

The other special case is when we have "some text needed" followed by a comma, and some other stuff. So we'll check to see if your cleaned-up string starts with "some text needed," (notice the comma). I like to handle that by using the stuff function. STUFF is great for when you need to get rid of some number of characters from the start of your string. So here I'm removing all the characters up to that comma, and adding 2 (one because the stuff function needs the index of the next character, and one more because if i just take len(@Slug) it won't account for the comma).

Then of course the default case is to just echo the original string.

declare @slug nvarchar(128) = 'some text needed'
select
    String,
    TrimmedString,
    case
        when TrimmedString = @slug then ''
        when TrimmedString like @Slug + ',%' then stuff(TrimmedString, 1, len(@Slug) + 2, '')
        else TrimmedString
    end
from #data
cross apply
(
   select TrimmedString = trim('[] ' from string)
) b

Note, if you're running a version of SQL Server that doesn't support TRIM , you can just replace that function call with the code you already had working. This is just a shortcut if you have access to it.

Changing Case When to ‘some text needed,%' (adding comma) helps deal with issue and returns everything without square brackets.

SELECT 
      A.*,
      CASE 
          WHEN B.YourColumn LIKE '
          THEN STUFF(B.YourColumn, 1, CHARINDEX(',', B.YourColumn + ','), '')
          ELSE REPLACE(REPLACE(B.YourColumn, '[', ''), ']', '')
      END as manipulated_column
    FROM 
      TableA AS A
    JOIN 
      TableB AS B 
      ON A.JoinColumn = B.JoinColum

However, I am unable to SELECT only the second part of script after the comma. If string has 2 parts separated by comma I only want to select everything after first comma, if multiple commas then Only string after comma until third comma (excluding third comma).

Hope it helps explain the issue