THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

T-SQL stored procedure for finding/replacing strings in a text file. Really?

I know people have been doing all sorts of things with T-SQL, and I have absolutely no issue with people trying to push the limit of what T-SQL can do, or what you can use it to accomplish, especially when it’s for demonstration or pedagogical purposes, or as an intellectual exercise. But then I bumped into an article on writing a T-SQL stored procedure to find and replace strings in a text file. That really unsettled me!

Sure, when you are in a hurry, you need to grab a tool--any tool--to get the job done, and T-SQL happens to be that tool immediately within the reach, go right ahead. But that doesn’t mean T-SQL is the right tool for the task of manipulating strings in a text file, or something worth recommending for that task.

In my opinion, T-SQL is not the right tool because there exist numerous other tools that are far better at dealing with strings in a text file.

Okay, what if T-SQL is the only way you know? Well, in that case I’m sorry! But why not invest some time in learning a text processing tool?

On the other hand, I’m always wary about saying something is terribly bad just because I’m ignorant or because of my narrow experience. I’m curious to learn if there is a good case out there for a T-SQL stored procedure for finding/replacing strings in a text file.

Published Friday, January 27, 2012 1:33 AM by Linchi Shea



Szymon Wójcik said:

Makes me think of similar nightmare I came across once - xp_cmdshell had to be enabled because one stored procedure used it to run bcp in order to results of another sproc to text file.

January 27, 2012 1:04 AM

Eugene Lobanov said:

Looks like using GO TO. Old school, poor design. =)

January 27, 2012 1:16 AM

Ken Lee said:

Just the idea of substituting a text string in a file without reviewing or considering the consequences gives me the shivers.

OK, using the right tool is a good point too.

January 29, 2012 3:16 AM

Oz said:

I can imagine that I would try to do that, if I would have say thousands of files that need modification and the info where the files are located and the criteria which makes these modifications necessary reside within a sql db.

January 30, 2012 8:49 PM

Mona said:

I am currently searching for a procedure to replace a string in a file because I use the bcp utility to automatically export queries & output the files to an FTP.

For sure its not a safe tool but its very powerful, and in my case I have to modify something in the files created before copying it to the FTP to fix an encoding problem.

So its a good tool if its well used by someone who knows what they are doing.

August 7, 2014 5:52 AM

Dave Clary said:

If all you have is a hammer, everything looks like a nail.

The beauty of MS tools is that there are lots of different ways to do anything -- so if you are a DBA tasked with a job, and no access to other tools to do something, it becomes very helpful to find that you can write some code in TSQL that (while obviously able to be much better coded elsewhere) meets your needs.

January 2, 2015 1:30 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement