CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Sathish
Sathish

Reputation: 2066

SQL Server: Is it possible to simplify the following query WHERE clause?

I have the following SQL query with ten conditions in the WHERE clause (with a OR). 

DECLARE @profile TABLE (content VARCHAR(MAX))
INSERT INTO @profile(content)
VALUES ('<some_text>dbo.ConcatenateTitles([title].title_id,59<some_text>')
, ('<some_text>dbo.ConcatenateTitles(title.[title_id],59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( title.title_id,59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( title.title_id, 59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( [title].[title_id],59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( [title].[title_id], 59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( title.title_id ,59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( title.title_id , 59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( [title].[title_id] ,59<some_text>')
, ('<some_text>dbo.ConcatenateTitles( [title].[title_id] , 59<some_text>')
--SELECT * FROM @profile

SELECT * FROM @profile
WHERE 1=1 AND 
(
       content LIKE '%dbo.ConcatenateTitles(/[title].title_id,59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles(title./[title_id],59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( title.title_id,59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( title.title_id, 59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( /[title]./[title_id],59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( /[title]./[title_id], 59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( title.title_id ,59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( title.title_id , 59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( /[title]./[title_id] ,59%' ESCAPE '/'
    OR content LIKE '%dbo.ConcatenateTitles( /[title]./[title_id] , 59%' ESCAPE '/'   
)

All the conditions separated with the OR are similar (but with an extra space or [ characters). Would it be possible to simplify these ten conditions into one or two condition(s) (for example, with a PATINDEX)?

Edit: To give a bit of background on what I am trying to achieve:

MyTable(MyTable_id INT , Content varchar(max))

The data is similar to the data specified in the TABLE variable above.

I need to replace the 'dbo.ConcatenateTitles(title.title_id,59' (and its variations, with a white space or with ]) occurrances with 'dbo.ConcatenateTitles(title.title_id,4' I have used the query above to find out how many rows would be affected (800 rows affected out of 150K rows), and thought to run one update statement for each variation.

UPDATE MyTable SET content = REPLACE(content, 'dbo.ConcatenateTitles(title.title_id,59', 'dbo.ConcatenateTitles(title.title_id,4')
WHERE content LIKE '%dbo.ConcatenateTitles(title.title_id,59%'

Is there a better alternative? I do not want to change the other content of the column (i.e. except the 'dbo.ConcatenateTitles(title.title_id,59' [and its variations] bit, everything else needs to be preserved as it currently is).

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270291

I suspect the following might what you want:

   replace(replace(replace(content, ' ', ''), ']', ''), '[', '') LIKE
       '%dbo.ConcatenateTitles(title.title_id,59%' ESCAPE '/'

Of course, this is not exact. A closer approach would be:

   replace(replace(replace(replace(content, '[title]', 'title'
                                  ), '[title_id]', 'title_id'
                          ), ', ', ',')
                  ), ' ,', ',')
          ), '( ', '(') LIKE
       '%dbo.ConcatenateTitles(title.title_id,59%' ESCAPE '/'

As a side comment: a little regular expression support in SQL Server would go a long way.

Upvotes: 1

Related Questions