0

I have this sql query:

SELECT
    [Id],
    [Content]    
FROM 
    [MyTable] with (nolock)
where
    Content is not null
    and (PATINDEX('%"number" : "[0-9]%', Content) > 0
         or PATINDEX('%"number":"[0-9]%', Content) > 0
         or PATINDEX('%"number" :"[0-9]%', Content) > 0
         or PATINDEX('%"number": "[0-9]%', Content) > 0
         --del
         or PATINDEX('%"del":"[0-9]%', Content) > 0
         or PATINDEX('%"del":"[0-9]%', Content) > 0
         or PATINDEX('%"del":"[0-9]%', Content) > 0
         or PATINDEX('%"del":"[0-9]%', Content) > 0
)

On my server, with caches cleared, it takes more than two minutes to return around 400 rows, maybe because I have a lot of conditions with or.

I've created the query that way because of the possibilities of the "number" string in the Content column of having a space or not between ":" and "number" string or the next digit.

Is there a way to reduce from the or conditions?

wildplasser
  • 43,142
  • 8
  • 66
  • 109
Buda Gavril
  • 21,409
  • 40
  • 127
  • 196
  • `REPLACE(' ', '')` white space then a single `PATINDEX('%"number":"[0-9]%'` ? – Alex K. Mar 29 '17 at 16:24
  • Sounds like you have a lousy data structure. You might consider fixing the data. – Gordon Linoff Mar 29 '17 at 16:24
  • I am not allowed to change a space with an empty string. This data comes from the clients that integrated an api and they can send the json as they want, as long as it is valid. – Buda Gavril Mar 29 '17 at 22:25

1 Answers1

1

Try it

SELECT
    [Id],
    [Content]    
FROM 
    [MyTable] with (nolock)
where
    Content is not null
    and (PATINDEX('%"number":"[0-9]%', replace(Content,' ','')) > 0
         or PATINDEX('%"del":"[0-9]%', replace(Content,' ','')) > 0)

and you can remove Content is not null part also

Vecchiasignora
  • 1,275
  • 7
  • 6
  • I am not allowed to change a space with an empty string. This data comes from the clients that integrated an api and they can send the json as they want, as long as it is valid. – Buda Gavril Mar 29 '17 at 22:25
  • @BudaGavril: The suggestion from Vecciassignora will not make any changes to your data, it will simply look for all records that match the `PatIndex()` on the `Context` field if the spaces in there would have been removed. It's like saying `WHERE field + 5 = 10`; this would return you all records where `field` equals 5, but it would not make any changes to `field`. – deroby Mar 31 '17 at 15:49