Data - Do you avoid spaces and empty lines at the start of character columns?
  v8.0 Posted at 21/05/2020 3:37 PM by Christian Morford-Waite

​​​​​​​Text in character columns (char, varchar, text, nchar, varchar, text) can start with spaces or empty lines which is usually data entry error.​ 

The best way to avoid this issue is to handle whitespace in the middle-tier before it reaches the database.​​

Here’s an example of removing whitespace and carriage returns in the middle-tier using Regex:​

static string Trim(string inputText)
  Match m = Regex.Match(inputText, @"[^\s]");
  return m.Success ? inputText.Substring(m.Index) : inputText;

​​Figure: C# Removing whitespace and carriage returns in middle-tier

The code above:

  • Uses Regular Expressions (Regex) to match the first non-whitespace character (includes tabs, spaces, line feeds and carriage returns).
  • Retrieves the index of the character
  • Returns the text from the character onwards, thus removing the whitespace at the start
This code could be triggered in the middle-tier before inserting into the database.​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address: