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

avatar image

Gary Clinton
11 1 1 3

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

4 answers: sort voted first

I think this is what you want:

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

answered Oct 12, 2009 at 03:35 PM

avatar image

Jack Corbett
1.1k 3 4 7

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

David 1
1.8k 3 5

(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

avatar image

Adam Haines
91 2 2

(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.

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:

x1066
x85
x3

asked: Oct 12, 2009 at 03:26 PM

Seen: 4468 times

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

Copyright 2016 Redgate Software. Privacy Policy