Using T-SQL to remove unwanted characters

Let’s pretend you have a column (in a table) in MS SQL Server and it has some stuff that you don’t want to be there. For example if you only wanted a column to have alpha or only numeric data you could essentially do a search a replace on that column using  a simplified regular expression syntax that the patindex in T-SQL provides.

Below is an example of the solution.

select top 1 * from Person

while @@rowcount > 0
        update  Person
        set     LName = replace(LName, substring(LName, patindex('%[^a-zA-Z. ]%', LName), 1), '')
        where   patindex('%[^a-zA-Z. ]%', LName) <> 0

The first line is only necessary for the @@rowcount to be set to something greater than 0. If you have an update on that table you need to do, you could do that instead. You could also add some more logic, add variables, etc.

The update statement is called multiple times until no more updates are needed. It is really pretty awesome the way that it keeps affecting less and less rows until no more are affected ad the while loop exits. It feels kind of recursive.

You don’t really have to understand it to make use of this. The important thing to note here is that the first parameter of patindex is the pattern it is matching on. In this case, I am matching (accepting) only lowercase a-z and uppercase A-Z and period and space (notice the space at the end before the ]). You can change this pattern to any valid pattern that patindex accepts.

References:
Docs for patindex
Pattern syntax
I got the idea from here.

Looping through an array of regular expressions in JavaScript

Regular Expressions in JavaScript are easy (assuming you know regular expressions ;). This is NOT a tutorial on regular expressions. I recommend the this site as an intro to Regular Expressions using JavaScript. What this entry does cover is how to create a regular expression in JavaScript. The recommended way is var regex = /int/ This will create a RegExp object that matches on the pattern int. This is not very interesting but it does illustrate that lack of double-quotes. This a very nice syntax that does NOT require the regular expression to be a literal string which in turn require the escaping of backslashes. Here is an example of a match on a digit var regex = /\d/ There are times like when getting input from the user or a database or something that you need to use the string syntax. In this case you do have to escape backslashes. This site explains this concept in more depth than I do here. var regex = new RegExp("\\d"); By default Regular Expressions in JavaScript match based on case and only match on the first occurrence. You can change that by using one of the following options. var regex = /int/gi or var regex = new RegExp("int", "gi"); Now that we know how to create a regex expression you may be starting to see how we can use them in arrays. Arrays in JavaScript can be done a few ways. var myArray = new Array(/int/gi, /\w/gi); or var myArray = new Array( new RegExp("int", "gi"), new RegExp("\\w", "gi" ); or var myArray = new Array(5); myArray[0] = new RegExp("int", "gi"); myArray[1] = /int/gi