How to clean a columns with a dots at the beginning and end of a text using T SQL for SQL Server?

Question

Description:

• I have a column(EmailsAdress) on a table(BusinessUsers) on my databases that stores email address.

Problem:

• Some of the rows of data have a dot at the beginning of this column for example [email protected] (The dot i want to get rid of is the dot just before the charater j in jane)
• Some of the rows of data have a dot at the end of this column for example [email protected]. (The dot i want to get rid of is the dot just after the charater m in com)

Solution:

• I am using SQL Server 2008 is there a T SQL statement that can do that

Thanx in advance

Answer 1

Using native functionality - SUBSTRING

With a period at the start

UPDATE BUSINESSUSERS
   SET emailsaddress = SUBSTRING(emailsaddress, 2, LEN(emailsaddress))
 WHERE emailsaddress LIKE '.%'

With a period at the end

UPDATE BUSINESSUSERS
   SET emailsaddress = SUBSTRING(emailsaddress, 1, LEN(emailsaddress)-1)
 WHERE emailsaddress LIKE '%.'

Answer 2

If by "clean" you mean remove the dots in the address name but not the domain, you could do something like:

Update BusinessUsers
Set EmailAddress = Replace(Substring(EmailAddress, 1, CharIndex('@', EmailAddress) - 1), '.', '')
                    + Substring(EmailAddress, CharIndex('@', EmailAddress), Len(EmailAddress))
From BusinessUsers

To remove trailing dots, you can do:

Update BusinessUsers
Set EmailAddress = Substring(EmaillAddress,1,Len(EmailAddress)-1)
From BusinessUsers
Where Right(EmailAddress,1) = '.'

Answer 3

The other answers are good, especially if you have to live in pure T-SQL land. However, just as another option, you could solve problems like this with a little Regex magic. Since you're using SQL 2008, you could leverage .NET. Here's some VB code to make 2 CLR UDFs that you can re-use over and over:

Option Explicit On
Option Strict On
Option Compare Binary
Option Infer On

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Text
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

    <Microsoft.SqlServer.Server.SqlFunction()>
    Public Shared Function IsRegexMatch(ByVal input As SqlString, ByVal pattern As SqlString) As SqlBoolean
        If input.IsNull OrElse pattern.IsNull Then Return SqlBoolean.Null
        Return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnorePatternWhitespace Or RegexOptions.Singleline Or RegexOptions.Multiline)
    End Function

    <Microsoft.SqlServer.Server.SqlFunction()>
    Public Shared Function RegexReplace(ByVal input As SqlString, ByVal pattern As SqlString, ByVal replacement As SqlString) As SqlString
        If input.IsNull OrElse pattern.IsNull OrElse replacement.IsNull Then Return SqlString.Null
        Return Regex.Replace(input.Value, pattern.Value, replacement.Value, RegexOptions.IgnorePatternWhitespace Or RegexOptions.Singleline Or RegexOptions.Multiline)
    End Function

End Class

Once you add these UDFs to SQL Server, you can solve your problem with a dirt simple call like this:

update BusinessUsers
set EmailAddress = dbo.RegexReplace(EmailAddress, '^\.|\.$', '')

It's a great general purpose solution to help with all sorts of similar text manipulation problems you might encounter in the future.