SQL Index on part of a field
Posted on 2007-04-11
I have a character field in my database that contains 2 separate pieces of information, work order and job. Both fields are character and are separated by a - (dash) in the field. I created a view that has taken the field and broken it into it's components for easy reporting, but am concerned that I do not have an index over the Work Order field. Is there a way to create an index on a subset of data in a field?
Also, the work order field does vary in size from 5 to 7 characters. I would like to utilize the following code to break the field into it's subcomponents...
case when charindex('-',b.paprojnumber)>1 then left(b.paprojnumber,charindex('-',b.paprojnumber)-1)
else b.paprojnumber end
How can I do this?