x

Strip parentheses from SELECT TOP (100) PERCENT

I have a "memBaseSQL" field where I want to script out the parentheses from SELECT TOP (100) PERCENT statements.

more ▼

asked Oct 12, 2009 at 03:26 PM in Default

Gary Clinton gravatar image

Gary Clinton
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort oldest

I think this is what you want:

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

answered Oct 12, 2009 at 03:35 PM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12, 2009 at 04:52 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Matt, I'm not sure why you would want to go to the CLR and regex just to remove parentheses in this scenario when REPLACE or STUFF will work just fine.
Oct 12, 2009 at 11:04 PM Jack Corbett
Jack, pretty much for the reason I said above - REPLACE will replace all instances, not just the ones around the SELECT TOP (100) PERCENT. So SELECT TOP (100) PERCENT ((a + b) * c) FROM table would be an example where it wouldn't be sufficient.
Oct 13, 2009 at 04:53 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12, 2009 at 06:13 PM

David 1 gravatar image

David 1
1.8k 1 3

not saying that this is the case here, but using TOP 100 PERCENT is one way of adding an ORDER BY clause to a view. Without it, you get an error message.
Oct 13, 2009 at 11:20 AM Kev Riley ♦♦
Views cannot be ordered so adding an ORDER BY clause with the 100 PERCENT clause achieves nothing.
Oct 13, 2009 at 06:08 PM David 1
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 12, 2009 at 06:39 PM

Adam Haines gravatar image

Adam Haines
91 1

What happens if it's not 100, or there are two or three spaces?
Oct 12, 2009 at 07:51 PM Matt Whitfield ♦♦
Then it does not get replaced. The requirement is to replace top (100). If other values need to be replaced I would consider running the same UPDATE with a differnt threshold. I would hope this is a one time UPDATE.
Oct 13, 2009 at 10:50 PM Adam Haines
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x69
x3

asked: Oct 12, 2009 at 03:26 PM

Seen: 3616 times

Last Updated: Oct 13, 2009 at 08:50 AM