Solved

SQL Server Replace - Character Strip

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

I need to update a field in a database table and strip out all characters except:
- Upper and Lower case alphanumeric are fine aA1
- The plus sign (+)

I want to strip all other characters from the field.

Thanks all
0
Comment
Question by:sbornstein2
  • 2
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36489174
Unfortunately, there is not a built-in regular expression replace in T-SQL, but here is an example using CLR if you are adventurous: http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx

As an alternative, you could use a WHILE loop to check for existence of a value LIKE '%[^A-Za-z0-9+]%' and do iterative REPLACE() statements. Or using a numbers tables, especially if we are not dealing with large number of rows or long character strings, you could pull apart each character checking if they meet the regex above and if so substitute for ''. Then you can pull the string back together in character order.

I am about to go in transit, so can show an example; however, it will not be until later.
0
 

Author Comment

by:sbornstein2
ID: 36489295
What about a function that can do this?  I thought you could use regular expressions in SQL PatIndex.
0
 
LVL 8

Accepted Solution

by:
venk_r earned 250 total points
ID: 36489299
Try the below function and see if that helps.

ALTER Function [dbo].[KeepNonAlphaCharacters](@Temp VarChar(1000)) Returns VarChar(1000)
 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
 
 Select dbo.KeepNonAlphaCharacters('Abc1234def5678ghi90jkl+;-')
0
 

Author Closing Comment

by:sbornstein2
ID: 36489328
perfect 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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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