Email and mobile number validation in SQL Server 2008 R2

[ashish_psl]

We have to validate email address and mobile number using SQL query. We are using SQL Server 2008 R2.

We tried using the ibuilt function “sp_OACreate” in side user define function which creates OLE objects runtime and validate using regex but it is taking more time if i want to validate more than 1500 records.

Please suggest effective solution…

[mikey_w]

Any reason you can’t do this at the application level?

[ashish_psl]

We are processing the excel file, converting excel into xml and then xml is push to DB.

Now, excel file contains more than 5000 records and on application level we are not iterating individual row. Application just convert excel to xml and send to DB

[molona]

Wouldn’t be better to validate the addresses in Excel with a macro? Or are you comparing them with e-mail addresses already in the system?

[WolfShade]

Just my $0.03642 worth… since you’re already processing an Excel file, instead of converting to XML, why not convert it to a query object and iterate through it in whatever server-side language you’re using, validate that before inserting into the database?

Actually, depending upon which language you’re developing in, you might even be able to validate the XML on-the-fly.

V/r,

[mikey_w]

@ashish_psl

We are processing the excel file, converting excel into xml and then xml is push to DB.

Now, excel file contains more than 5000 records and on application level we are not iterating individual row. Application just convert excel to xml and send to DB

Depending on the application language you are using, my guess is that the programming language has better tools for checking e-mails than SQL Server does…

I would do it in the application. And if there isn’t code to check e-mails in the application, then write some!

[mikey_w]

@WolfShade

Just my $0.03642 worth… since you’re already processing an Excel file, instead of converting to XML, why not convert it to a query object and iterate through it in whatever server-side language you’re using, validate that before inserting into the database?

Actually, depending upon which language you’re developing in, you might even be able to validate the XML on-the-fly.

Sounds like a winning idea to me!