Press "Enter" to skip to content

String Parsing with SQLCLR

Josh Darnell would like you to give CLR a try:

The basic problem is this: given a string of arbitrary characters, return a new string containing only numbers. If there are no numbers, return NULL.

One use case for a function like this would be removing special characters from a phone number, although that’s not the specific goal of this function.

Doing string manipulation directly in SQL Server using T-SQL is somewhat infamously slow. This is stated very nicely in Aaron Bertrand’s (b | t) blog post Performance Surprises and Assumptions : STRING_SPLIT() (note he’s talking about splitting strings, the emphasis is mine to illustrate the broader point):

Throughout, my conclusion has been: STOP DOING THIS IN T-SQL. Use CLR or, better yet, pass structured parameters like DataTables from your application to table-valued parameters (TVPs) in your procedures, avoiding all the string construction and deconstruction altogether – which is really the part of the solution that causes performance problems.

For the life of me, I really don’t get why CLR is supposed to be so scary for DBAs. The best answer I have is that they matched the .NET term “unsafe” (which means unmanaged code) and DBAs, without a .NET background, interpreted that the wrong way.