Skip to content

Databases

How to Rectify Mal-formatted Records in a Database Table?

Executing an Update query on a production database is like driving full speed in opposite direction in a one-way traffic! You never know when your vehicle meets with an accident. A little carelessness in putting up the where clause can create havoc in your database. And an irreparable damage if you forgot to include the where clause!

But there are times when you can’t avoid the update on production server. Due to poor coding and testing practices, during initial deployments of your application to production server you may encounter formatting issues in some of the records. See for example, in the following figure: the records that are marked inside the three red rectangles.

Takeaways from this Article

  1. Learn escape sequence. In this example, we’ll escape single quote in T-SQL by using two single quotes (not double quotes).
  2. Implementation of While Loop.
  3. Implementation of SQL Server table variable.

Note: Example shown here is using Microsoft SQL Server – 2012 edition but should work equally well with SQL Server 2005 and 2008 / R2. To some extent this idea can also be used to other RDBMS as well.

DB Table Records

Yes, those extra single quote at the beginning and end of the phone number. You might have deployed a fairly stable version of your application to production but unfortunately it may happen. Though these records are correct their mal-formatted structure will create issues in search operations. If you’re not rectifying them, you might need to perform some extra operations  which might affect the performance of your application.

In this example, you see less than 10 records that needs to be updated. You can do this manually without executing any query. But what if you’ve say 100+ records hidden in a table with million records and that too not in any sequence? Then you must use some advanced queries to update the query.

How to fix it?

Observe if there’s a pattern in the mal-formatted records. If yes, your job of updating the records become simple. In the above example, what’s the pattern? It’s the single quote. To convert the mal-formatted records to well-formed one, you just need to replace the single quotes. What could be the possible steps to rectify this issue:

  1. Identify these records.
  2. If the records are less in numbers you can update it one by one.
  3. If the records are high in number then you need to opt for some row wise operation.
  4. For row wise operation, I don’t suggest cursors as it may affect performance, it’s better to use a table variable with while loop. But choice is yours.

Now let us create our sample table using the following code:

Create-Table

 

 

 

The above code will create the desired table. Insert a few sample records as shown in the first image. Now have a look at the following figure. Yes, you got it… the figure not only identifies the mal-formatted records but has also rectified them.

DB-records

 

 

 

 

Now check the following query. The above output is produced by line# 14 of below code. Line# 14 has single quotes only, don’t confuse it with double quote. Apart from going through the code, carefully read the comments also to fully understand the code.

Stored-Proc-code

 

After executing the above query you’ll get the well-formed records. How was it? Please share your views in comment section.

 

 

 

 

 

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)