Data - Do you avoid spaces and empty lines at the start of character columns?
21/05/2020 3:37 PM by
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)Figure: C# Removing whitespace and carriage returns in middle-tier
Match m = Regex.Match(inputText, @"[^\s]");
return m.Success ? inputText.Substring(m.Index) : inputText;
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.
Do you feel this rule needs an update?