CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Remove some part of string

I have a query with order by clause as shown below.

SET @SQLquery = 'SELECT Cola,Colb FROM Test
              ORDER BY Cola DESC,Colb'

I have a variable which contain query with order by clause.

Note: I just want to remove order by clause ORDER BY Cola DESC,Colb. I may have multiple column in ORDER BY so basically the @SQLQuery will be dynamic.

Upvotes: 0

Views: 55

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Try this:

SET @SQLquery = LEFT(@SQLquery, PATINDEX('%ORDER BY%', @SQLquery) - 1)

If your are not sure whether ORDER BY is included in @SQLquery or not, then you could use:

SET @SQLquery = LEFT(@SQLquery, CASE WHEN PATINDEX('%ORDER BY%', @SQLquery) <> 0 
                                   THEN PATINDEX('%ORDER BY%', @SQLquery) -1
                                   ELSE LEN(@SQLquery)
                                 END)

Upvotes: 1

Related Questions