sql server - The most efficient way to compare first character of string in SQL - Stack Overflow

I'm working on some legacy queries in a company and I've stumbled upon a line

WHERE MobilePhone like '0%'

I can see that it's a simple filter to pass only valid mobile phones, but, from what I know, it's really not the best way to do that - 'like' is quite a resource-intensive operation. That is a problem because this query executes every day and works on quite big amounts of data.

Is there a better way to do that?

I'm working on some legacy queries in a company and I've stumbled upon a line

WHERE MobilePhone like '0%'

I can see that it's a simple filter to pass only valid mobile phones, but, from what I know, it's really not the best way to do that - 'like' is quite a resource-intensive operation. That is a problem because this query executes every day and works on quite big amounts of data.

Is there a better way to do that?

• sql
• sql-server

Share Improve this question Follow edited Jan 13 at 11:55 Mark Rotteveel 110k231231 gold badges156156 silver badges225225 bronze badges asked Jan 8 at 13:28 LayronLayron 4555 bronze badges 12

• 1 First of all, that query doesn't pass all valid numbers (e.g. +447.... is a valid representation). This is a good reference here – DavidG Commented Jan 8 at 13:30
• 3 This particular format of LIKE is sargable, because it only looks at the beggining of the string, so it'll make use of an index in the MobilePhone column. As long as there is such an index, the query should be quite fast. – Alejandro Commented Jan 8 at 13:32
• 2 Having said that, your best bet is to test what works best for your workload, though I would guess LEFT(MobilePhone, 1) == '0' might be faster (and you can argue is more readable) – DavidG Commented Jan 8 at 13:33
• 2 since creating new index is not an option for me Why not? Without an index, it really doesn't matter what query you use, anything is just going to perform a full table scan, with very slow results if the table is big. You need that index for this query to be fast. – Alejandro Commented Jan 8 at 14:01
• 1 I'm not allowed to update tables and there is only one existing index on PK without fixing that first you won't be able to improve performance. Indexes are key to query speed. Add the index and the difference will be quite big. – Alejandro Commented Jan 9 at 15:46

 |  Show 7 more comments

1 Answer 1

Sorted by: Reset to default 3

What you said about LIKE being resource intensive might be true most of the time. But in this case, SQL Server needs only to check the first character of each mobile phone number, which isn't so bad.

Note that, as written, your query is sargable, meaning that it can take advantage of an index. The following index should work well:

CREATE INDEX ON yourTable (MobilePhone);

To use the index, SQL Server can find all matching records by simply scanning the portion of the B-tree index which starts with 0.