Find How Many Times a Specific Character Is Repeated In a String

Recently I came across an email address in the database at work that had 2 '@' symbols (The offending email address looked something like this: 'name.last@@aol.com'.)  Clearly the front end was not doing it's job and we had bad data.  I was curious to see if there were any other email addresses that had more than one @ so I wrote the below query.

SELECT

     Client_id

     ,Email

FROM

     Client

WHERE

     LEN(Email) - LEN(REPLACE(Email, '@', '')) > 1

Now let's break it down:

LEN returns the number of characters in the field.

LEN('name.last@@aol.com') returns 18 which is the number of characters in the string.


REPLACE('name.last@@aol.com', '@', '') returns the string without the '@' symbol.  The first argument is the string you want to search ('name.last@@aol.com').  The second argument is the character you are searching for ('@').  The third argument is what to replace the '@' with ('') in this case nothing (empty string).  This will just take the '@' out of the string and return the string without it.


LEN(REPLACE('name.last@@aol.com', '@', '')) then returns the number of characters in the string after you replace the '@' with an empty string.  


If you take the LEN of the full string and subtract the LEN of the string after you replace the character you are trying to count you are left with the number of times the search character is in the string. 


Compare this to 1 in the WHERE clause and you have all Client_ids and their Emails where the email has 2 @'s.


You can use this for other things as well, but this is how I used it today.