Clever Geek Handbook

How do I search for a specific pattern in a database field?

I have a column with fields C5, C6, C3, CC, CA, CD, etc.

I want to find all columns that have a letter and then a number like C5 and C6.

I know if yes:

SELECT * FROM TABLE WHERE FIELD LIKE 'C%'

It will also return CC, CA, CD, but I don't want that, I just want to return numbers. I also want to update this column by removing the number, so C5 becomes C and C6 becomes C, etc.

0


source to share


4 answers


The request will be SELECT * FROM TABLE WHERE FIELD LIKE 'C[0-9]%'



+3


source


Try to start here



The Microsoft link for Like has information about expressions that you can use in this type of statement.

+1


source


What you really need to do is redesign the structure, since you shouldn't be storing data that way. Children's desk is a way to store this kind of data and then you can query and update easily. The catch with the accepted solution is that you will still have a problem if the text does not start with C and the update is imprecise at best if there are multiples with C5, C6 in the same text group. This violates the overridden first rule of database design, never stores more than one piece of information in a column per row.

If you really want an exact way to find and update these records (even if you can't change the structure), you need to pull them into a linked temporary table named record_Id, and the value and and the updated bit column. Then find the ones with the number and update them and the update flag logged at the same time. Then, for record ids that have at least one flag, you then recreate the row and update the record itself. You also need to decide if you want two C-only values ​​if C5 and C6 are in the data string.

+1


source


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]%'
0


source






More articles:


All Articles