Sql-server – better alternative than LIKE for this query

sql serversql-server-2008

I am trying to retrieve data from a table where there are codes for projects which are identified by the first three numbers, the rest are unique. So, for example, the query is:

SELECT * FROM 
TableA
WHERE LEFT(acccode,3) IN ('121','131','141','151','161')

Now, I want this condition to be true for all these codes except for one. Say, for the first one, I want it to look four more characters for anything like '121-111%'. How can I alter my IN operator there or do I have to just go with a LIKE operator with something like:

SELECT * FROM
TableA
WHERE acccode LIKE '121-111%'
    OR acccode LIKE '131%'...

and so on…?

To clarify: codes starting with 121-111, 131, 141, 151, 161 are the ones I want to select.

All columns are nvarchar.

Best Answer

"Better" in what sense?

The LIKE version is sargable as you have no leading wildcards and can use index seeks so that is better in that respect.

In this case you could also consider

SELECT * FROM 
TableA
WHERE acccode LIKE '1[3-6]1%'
      OR acccode LIKE '121-111%'

As a simplification though the fully expanded version might be more efficient.

If you have no useful index you might prefer just using

SELECT * FROM 
TableA
WHERE LEFT(acccode,3) IN ('131','141','151','161') 
      OR acccode LIKE '121-111%'

Either way it looks as though there is some meaning buried in acccode that might be best storing in a separate column.

Related Question