Strip parentheses from SELECT TOP (100) PERCENT

I think this is what you want:

SELECT
    REPLACE(REPLACE(memBaseSQL, '(', ''), ')', '')
FROM
    table

I would use a regular expression CLR to do that - if you just replace brackets it will affect the whole statement, and could easily change the meaning of arithmetic expressions (for example - select ((1 + 2) * 3) = 9, whereas SELECT 1 + 2 * 3 = 7) or logical constructs.

If you were to use a regular expression

SELECT\s+TOP\s+\(([0-9]*)\)\s+PERCENT

And use the replacement expression

SELECT TOP $1 PERCENT

Then you will end up with the brackets removed just in that part of the statement.

Why use TOP (100) PERCENT at all? It achieves nothing useful and probably only indicates that the author of the code didn't know what they were doing and was relying on the "Query Designer" feature to write queries.

The objective is to replace the top operator that has parenthesis around the numerical value. With that said the best and easiest solution is to search for this exact scenario. There are only two real scenarios that you have to check for, top with a space and top without a space. The end result is a very simple replace function.

DECLARE @t TABLE(
memBaseSQL VARCHAR(100)
)

INSERT INTO @t VALUES ('SELECT TOP (100) PERCENT ');
INSERT INTO @t VALUES ('SELECT TOP(100) PERCENT ');
INSERT INTO @t VALUES ('SELECT TOP 100 PERCENT ');

UPDATE @t
SET [memBaseSQL] = REPLACE(REPLACE([memBaseSQL],'SELECT TOP(100)','SELECT TOP 100'),'SELECT TOP (100)','SELECT TOP 100')
WHERE [memBaseSQL] LIKE 'SELECT TOP(100)%' OR [memBaseSQL] LIKE 'SELECT TOP (100)%'

SELECT *
FROM @t

Here is another version that allows the use of multiple percentages.

DECLARE @t TABLE(
memBaseSQL VARCHAR(100)
)

INSERT INTO @t VALUES ('SELECT TOP (100) PERCENT');
INSERT INTO @t VALUES ('SELECT TOP(100) PERCENT');
INSERT INTO @t VALUES ('SELECT TOP 100 PERCENT');
INSERT INTO @t VALUES ('SELECT TOP (60) PERCENT');
INSERT INTO @t VALUES ('SELECT TOP(60) PERCENT');
INSERT INTO @t VALUES ('SELECT TOP(1) PERCENT');
INSERT INTO @t VALUES ('SELECT TOP (1) PERCENT');

UPDATE @t
SET [memBaseSQL] = 
    CASE 
    	WHEN memBaseSQL LIKE 'SELECT TOP([0-9][0-9][0-9])%'
    		THEN LEFT([memBaseSQL],10) 
    			+ SPACE(1) + SUBSTRING([memBaseSQL],12,3)
    			+ SUBSTRING([memBaseSQL],16,LEN([memBaseSQL]))
    	WHEN memBaseSQL LIKE 'SELECT TOP ([0-9][0-9][0-9])%'
    		THEN LEFT([memBaseSQL],10) 
    			+ SPACE(1) + SUBSTRING([memBaseSQL],13,3)
    			+ SUBSTRING([memBaseSQL],17,LEN([memBaseSQL]))
    	WHEN memBaseSQL LIKE 'SELECT TOP([0-9][0-9])%'
    		THEN LEFT([memBaseSQL],10) 
    			+ SPACE(1) + SUBSTRING([memBaseSQL],12,2)
    			+ SUBSTRING([memBaseSQL],15,LEN([memBaseSQL]))
    	WHEN memBaseSQL LIKE 'SELECT TOP ([0-9][0-9])%'
    		THEN LEFT([memBaseSQL],10) 
    			+ SPACE(1) + SUBSTRING([memBaseSQL],13,2)
    			+ SUBSTRING([memBaseSQL],16,LEN([memBaseSQL]))
    	WHEN memBaseSQL LIKE 'SELECT TOP([0-9])%'
    		THEN LEFT([memBaseSQL],10) 
    			+ SPACE(1) + SUBSTRING([memBaseSQL],12,1)
    			+ SUBSTRING([memBaseSQL],14,LEN([memBaseSQL]))
    	WHEN memBaseSQL LIKE 'SELECT TOP ([0-9])%'
    		THEN LEFT([memBaseSQL],10) 
    			+ SPACE(1) + SUBSTRING([memBaseSQL],13,1)
    			+ SUBSTRING([memBaseSQL],15,LEN([memBaseSQL]))
    ELSE memBaseSQL
    END
--REPLACE(REPLACE([memBaseSQL],'SELECT TOP(100)','SELECT TOP 100'),'SELECT TOP (100)','SELECT TOP 100')
WHERE 
       memBaseSQL LIKE 'SELECT TOP([0-9][0-9][0-9])%'
    OR memBaseSQL LIKE 'SELECT TOP ([0-9][0-9][0-9])%'
    OR memBaseSQL LIKE 'SELECT TOP([0-9][0-9])%'
    OR memBaseSQL LIKE 'SELECT TOP ([0-9][0-9])%'
    OR memBaseSQL LIKE 'SELECT TOP([0-9])%'
    OR memBaseSQL LIKE 'SELECT TOP ([0-9])%'


SELECT *
FROM @t