SQL Server Query PatIndex Question
Posted on 2011-09-06
I have a function as shown here that is stripping out any non alphanumeric characters except the plus (+) sign. I run an update statement against the whole table currently where I say for example:
Update Customer Set CustomerNumber = dboKeepNonAlphaCharacters(CustomerNumber)
The problem is this table has millions of rows so the logs grow when doing this and performance is slowed. How can I only update those records that have a non alphanumberic number like shown below to only update that subset of records to keep the log down?
Alter Function [dbo].[KeepNonAlphaCharacters](@Temp VarChar(35)) Returns VarChar(35)
While PatIndex('%[^a-zA-Z0-9+]%', @Temp) > 0 --AND PatIndex('+', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^a-zA-Z0-9+]%', @Temp), 1, '')