SQL query with Like operator with Leading Wildcards

Question

I have a query as follows:

select top (30000) * from table1 where licenseplate like '%as43bv%'

I have an index on licenseplate but the query will not hit the index due to leading wildcard.

Since the column queries license plate numbers full text search wont come to any benefit we are searching on some letter or number rather than words

The above query fetches millions of rows and brings the CPU usage around 100%.

Is there any efficient way to rewrite the query and make use of the indexes.

Aleksey Vitsko · Answer

What first comes to mind, is that if you are searching for some symbols inside the licenseplate that will always start, for example at 4th symbol (example data 123as43bvxxx ), and it will be always be fixed, you can consider adding computed persisted column to your table

alter table [table1]
add LicenseplateComputed as substring(licenseplate,4,50) persisted

And then create nonclustered index on your newly added computed column "LicenseplateComputed"

After this you will be able to query your table

select top (30000) * from table1 where LicenseplateComputed like 'as43bv%'

and it will perform efficient Index Seek

SQL query with Like operator with Leading Wildcards

One Answer

Add your own answers!

Ask a Question