Solved

SQL Server Query PatIndex Question

Posted on 2011-09-06
3
391 Views
Last Modified: 2012-05-12
Hello all,

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)
 AS
 Begin
While PatIndex('%[^a-zA-Z0-9+]%', @Temp) > 0 --AND PatIndex('+', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^a-zA-Z0-9+]%', @Temp), 1, '')
Return @Temp
 End

0
Comment
Question by:sbornstein2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36493410
Add a WHERE clause. Try with:
your_column_to_clean LIKE '%[^a-zA-Z0-9+]%'

You can also do it with the PATINDEX(), but I would try it with the LIKE.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36493419
In other words, like this:

UPDATE your_table
SET your_column_to_clean = dbo.KeepNonAlphaCharacters(your_column_to_clean)
WHERE your_column_to_clean  LIKE '%[^a-zA-Z0-9+]%'

Open in new window

0
 

Author Closing Comment

by:sbornstein2
ID: 36493443
cool thanks
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question