I have a column that has fields such as C5, C6, C3, CC, CA, CD, etc.
I want to be able to find all the columns that have a letter and then a number such as C5 and C6.
I know if I do:
SELECT * FROM TABLE WHERE FIELD LIKE 'C%'
It will return CC,CA,CD as well, but I don't want that, I want to just return the ones numbers. I also want to update this column deleting the number, so C5 becomes C and C6 becomes C and so on.
Microsoft's reference for Like has information about expressions you can use within that type of statement.
The query would be SELECT * FROM TABLE WHERE FIELD LIKE 'C[0-9]%'
What you really need to do is redesign your structure as you should not be storing data this way. A child table is the way to store this kind of data and then you can easily query and update. The propblem with the accepted solution is that you will still have trouble if the text doesn't begin with C and updating will be inaccurate at best if there are multiples with C5, C6 in the same text group. This violates the abolute first rule of database design, never store more than one piece of information in a column per row.
If you truly want an accurate way to find and update these records (even if you can't change the structure), you need to pull them out into a related temp table that has the record_Id and the value and and Updated bit column. Then find the ones with a number and update them and the update flag filed at the same time. Then for the record ids which have at least one flag, you would then recreate the string and update the record itself. You also need to decide if you want two values of just C if C5 and C6 are both in the string of data.
Use any of these
select LEFT(col,1) from @t where col like 'c[0-9]%'
select substring(col,1,1) from @t where col like 'c[0-9]%'