Replacing Data – Part 2

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? In my last article, I created a inline table valued…

Replacing Data – Part 1

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? The Transaction SQL language does come with a REPLACE() string…

String Functions – REPLACE()

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the REPLACE() function today. The REPLACE() function takes a source string, a search string and a replacement string as input. For each occurrence of the search string found in the source string, change the value to the replacement string. Again, I will be using my favorite movie title in this example. Who does not like the “Wizard of OZ?”. The example below replaces the word “of” with the word…

Searching Stored SQL – Part 4

There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…

Searching Stored SQL – Part 3

There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…

Searching Stored SQL – Part 2

There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…

Searching Stored SQL – Part 1

Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…