locked
Input validation RRS feed

  • Question

  • Hi

    In C# .NET I have the possible to create some validations of my data, with regulary expressions.  Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?

    By Mik
    Thursday, June 25, 2015 8:37 PM

Answers

  • Hi Postbil,

    I'd use the CLR FUNCTION (Common Language Runtime) to manipulate the string with Regular Expression (REGEX). It's more recommended in my opinion. It's faster and efficient.  It easy to implement!

    Sample:

    Regex.IsMatch (string,@"^[^\d]{2,}$"))

    Best Regards


    Ricardo Lacerda

    • Marked as answer by Postbil Sunday, June 28, 2015 5:03 PM
    Friday, June 26, 2015 5:23 PM

All replies

  • Yeah, you can use Microsoft SQL Server integration with the aid of the .NET Framework Common Language Runtime (CLR).

    A Fan of SSIS, SSRS and SSAS

    Thursday, June 25, 2015 8:55 PM
  • Not as such. You can use other SQL tools -- ETL is the general category.

    However, you can use CHECK ( .. LIKE..) constraints on the DDL in T-SQL today and hope that Microsoft gets the ANSI/ISO Standard SQL SIMILAR INTO predicate in the future if you really need full regular expressions. If you want to do it today, you can use CASE expressions or OR-ed expressions with LIKE and get a good regular expression check, too. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Staphen Sunday, June 28, 2015 11:03 PM
    Thursday, June 25, 2015 9:22 PM
  • Your data should already be sanitized before you insert it.

    This is the job of your ETL tool/Front end app.

    There are some fundamental checks and validations built in, you cannot insert a 'A' into a date field, and so on.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, June 25, 2015 9:27 PM
  • Hi Postbil,

    I'd use the CLR FUNCTION (Common Language Runtime) to manipulate the string with Regular Expression (REGEX). It's more recommended in my opinion. It's faster and efficient.  It easy to implement!

    Sample:

    Regex.IsMatch (string,@"^[^\d]{2,}$"))

    Best Regards


    Ricardo Lacerda

    • Marked as answer by Postbil Sunday, June 28, 2015 5:03 PM
    Friday, June 26, 2015 5:23 PM
  • Thanks to for all or  your answers. It has been very useful ..
    Sunday, June 28, 2015 5:06 PM