How do I split a value on multiple special characters into columns with distinct values in SQL Server?

Input:

ModelOutput_Prime_AtLevel(Revenue)/Spend_Prime_AtLevel()

Output :

I tried with this SQL:

SELECT
    CASE
        WHEN CHARINDEX('_', formula) > 0
            THEN SUBSTRING(name, 1, CHARINDEX('_', formula) -1)
            ELSE name
    END col1,
    CASE 
        WHEN CHARINDEX('_', formula) > 0
            THEN SUBSTRING(formula, CHARINDEX('_', formula) + 1, LEN(formula))
            ELSE NULL 
    END AS col2 
FROM
    tableb

But it will only split the first occurrence, not picking up the second occurrence

I am going to start by repeating my comment:
> Ideally? By not inserting such data into your database in the first place and splitting it before it gets anywhere near your table.

I'm also going to point out that in the comments you confirm that the string has no format, meaning that the number of columns is dynamic. This further says that T-SQL is the completely wrong place. Fix what ever it is that is generating these terrible strings and normalise your design (what ever that is).

That being said, you can do this. I don't explain this because I think it's a terrible idea; if you really want/"need" to use this then it's up to you to take the time to understand the solution and use it for your own needs (if you need to consume a dataset, you'll need to adjust this, and you're on your own there). It also does not give the results in your question because you ignore the first Prime but the second AtLevel; this solution always uses the first instance meaning AtLevel is the third column for your string.

It uses a "helper" function of mine as well, which I include the definition of. I assume by "special character" you mean anything that isn't an alphanumerical value. Finally, this uses SQL Serve 2022+ functionality. If you're on 2019 or prior you'll need to use a different splitter which provides an ordinal position (again, you're on your own there).

CREATE FUNCTION [fn].[PatternCharacterReplace] (@String varchar(8000),
                                                @Pattern varchar(100),
                                                @ReplacementCharacter varchar(1))
RETURNS table
AS RETURN
    --Copyright Thom A (https://larnu.uk) under CC-by-SA
    WITH N AS
        (SELECT N
         FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS
        (SELECT TOP (DATALENGTH(@String))
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
         FROM N N1,N N2,N N3,N N4) --10000 rows.
    SELECT STRING_AGG(CASE WHEN V.C LIKE @Pattern THEN @ReplacementCharacter ELSE V.C END, '')WITHIN GROUP(ORDER BY T.I) AS ReplacedString
    FROM Tally T
         CROSS APPLY (VALUES (SUBSTRING(@String, T.I, 1))) V (C);
GO

DECLARE @YourString varchar(8000) = 'This_is_a_(terrible)_idea/Why_would_you_do_this/Do_not_do(this)';
DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter nvarchar(20) = N',' + @CRLF + N'       ';

WITH RNs AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY SS.value ORDER BY SS.ordinal) AS RN
    FROM fn.PatternCharacterReplace(@YourString, '[^A-z0-9]',',') PCR
         CROSS APPLY STRING_SPLIT(PCR.ReplacedString,',',1) SS
    WHERE SS.value <> ''),
ColNos AS(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY ordinal) AS ColNo
    FROM RNs
    WHERE RN = 1)
SELECT @SQL = 
       N'WITH RNs AS(' + @CRLF +
       N'    SELECT *,' + @CRLF + 
       N'           ROW_NUMBER() OVER (PARTITION BY SS.value ORDER BY SS.ordinal) AS RN' + @CRLF + 
       N'    FROM fn.PatternCharacterReplace(@YourString, ''[^A-z0-9]'','','') PCR' + @CRLF + 
       N'         CROSS APPLY STRING_SPLIT(PCR.ReplacedString,'','',1) SS' + @CRLF + 
       N'    WHERE SS.value <> ''''),' + @CRLF + 
       N'ColNos AS(' + @CRLF + 
       N'    SELECT *,' + @CRLF + 
       N'           ROW_NUMBER() OVER (ORDER BY ordinal) AS ColNo' + @CRLF + 
       N'    FROM RNs' + @CRLF + 
       N'    WHERE RN = 1)' + @CRLF + 
       N'SELECT ' +
       STRING_AGG(CONCAT(N'MAX(CASE ColNo WHEN ', ColNo, N' THEN value END) AS ', QUOTENAME(CONCAT(N'Col',ColNo))), @Delimiter) WITHIN GROUP (ORDER BY ColNo) + @CRLF +
       N'FROM ColNos;'
FROM ColNos;

PRINT @SQL;

EXEC sys.sp_executesql @SQL, N'@YourString varchar(8000)', @YourString;

SQL Server XML and XQuery allow to implement it relatively easy.

The proposed solution will work starting from SQL Server 2017 onwards.

It is very easy to exclude certain words by adding an XPath predicate.

Notable points:

• TRANSLATE() function call is making input string as tokens
  separated by the underscore character only.
• XQuery distinct-values() call is removing duplicate values.

SQL

USE tempdb;
GO
SET NOCOUNT ON;
-- DDL and sample data population, start
DROP TABLE IF EXISTS dbo.tbl;
CREATE TABLE dbo.tbl (ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT INTO dbo.tbl (tokens) VALUES
(N'ModelOutput_Prime_AtLevel(Revenue)/Spend_Prime_AtLevel()'),
(N'one_two_three_four');
-- DDL and sample data population, end
DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
, @SQL AS NVARCHAR(MAX)
, @cnt INT, @i INT;
-- count total number of columns
SET @cnt = (SELECT MAX(cnt)
FROM dbo.tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r>' + 
REPLACE(TRANSLATE(tokens,'()/','___'), @separator, '</r><r>') + 
'</r></root>' AS XML)) AS t1(c)
CROSS APPLY (SELECT c.value('count(distinct-values(/root/r[text()]))', 'INT')) AS t2(cnt));
-- compose T-SQL statement SELECT clause
SET @SQL = 'SELECT t.*' + @CrLf;
-- loop through 
SET @i = 1;
WHILE @i <= @cnt BEGIN
SET @SQL += FORMATMESSAGE(', x.value(''(/root/r[%i]/text())[1]'', ''NVARCHAR(20)'') AS col%i%s', @i, @i, @CrLf)
SET @i += 1;
END
-- compose T-SQL statement FROM clause
SET @SQL += 'FROM dbo.tbl AS t
CROSS APPLY (SELECT TRY_CAST(''<root><r>'' + 
REPLACE(TRANSLATE(tokens,''()/'',''___''), ''_'', ''</r><r>'') + 
''</r></root>'' AS XML)) AS t1(c)
CROSS APPLY (SELECT c.query(''<root>
{ 
for $x in distinct-values(/root/r[text()])
return <r>{$x}</r> 
}
</root>'')) AS t2(x);';
PRINT @SQL;
EXEC sys.sp_executesql @SQL;

Output

Just another option using JSON and translate()

Not exactly the desired results, but at least consistent

Example

Declare @YourTable table (ID int,Input varchar(500))
Insert Into @YourTable values 
(1,'ModelOutput_Prime_AtLevel(Revenue)/Spend_Prime_AtLevel()')
Select A.ID
,Pos1 = JSON_VALUE(JS,'$[0]')
,Pos2 = JSON_VALUE(JS,'$[1]')
,Pos3 = JSON_VALUE(JS,'$[2]')
,Pos4 = JSON_VALUE(JS,'$[3]')
,Pos5 = JSON_VALUE(JS,'$[4]')
,Pos6 = JSON_VALUE(JS,'$[5]')
,Pos7 = JSON_VALUE(JS,'$[6]')
From  @YourTable A
Cross Apply (values (replace(translate(Input,'()/','___'),'__','_') ) ) B(CleanString)
Cross Apply (values ('["'+replace(string_escape(CleanString,'json'),'_','","')+'"]') ) C(JS)

Results