Creating a computed(possibly hidden) column using COLLATE SQL

Question

I'm using SSMS 18.

I have a column with 500,000,000 records that are codes. Those codes begin with a number of characters, that represent an object, followed by random characters. For example:

AZ4658 & TU874U = A-Z

sd8udf & kl8udf = a-z

0huoer & 0huoer = 0

3bhlbc & 3jhkla = 3

5bfdsg & 5byfdu = 5

9nbgyc & 98cnjj = 9

In my 'WHERE' clause, I have: WHERE [Code_Records] COLLATE Latin1_General_BIN Like '[A-Z]' or WHERE [Code_Records] COLLATE Latin1_General_BIN Like '[a-z]' or WHERE [Code_Records] COLLATE Latin1_General_BIN Like '[0]' or WHERE [Code_Records] COLLATE Latin1_General_BIN Like '[3]' etc

However, this is extremely slow as the index negates the function(or vice versa?). Is there a way to insert into a computed column using the COLLATE function and then index that column?

Thanks, I'm a year 1 IT apprentice so basically everything is new to me! Aidan

Answer 1

You're missing a % wildcard at the end of the LIKE expression:

WHERE [Code_Records] COLLATE Latin1_General_BIN Like '[3]%' 

Alternatively, you can use Substring() to get the first character and match that.