Sensei Cris

SQL Server Tips, Tricks, and other resources

In this section of the site you will find SQL tips, code, samples to common everyday tasks. If its not here you can certainly find it in many other places. One of the best resources I have found on the internet regarding SQL/TSQL is the site SQL Authority. It is very likely the question you need answered has already been asked and answered there.

1. How to search for text inside a stored procedure

   I frequently need to list all the stored procedures in a database which access a
   certain table, field, or udf. This helps me document what code needs changing as
   part of a bug fix or enhancement. Simply put, this little bit of code finds all
   the stored procedures in the given database that have the specified text in the
   body of the stored procedure.

   Use DatabaseName
   Go
   SELECT [Name] AS Procedure_Name,
       OBJECT_DEFINITION(object_id)  AS Procedure_Body
   FROM SYS.PROCEDURES
   WHERE OBJECT_DEFINITION(object_id) LIKE '%TextToSearchFor%'

2. LTRIM RTRIM doesn’t always work

   It is a frequent occurrence that we must remove leading and trailing whitespaces
   from a string before additional processing or sending it to another layer in an
   application. We can’t always control how the data is entered. The data might come
   from another system, a data conversion, an old application, or from an application
   which had poor quality control. In some of those cases a whitespace might not be
   entered or saved in the system as character 32 which is a whitespace
   entered in a keyboard. If that happens, SQL built in functions for trimming whitespaces
   might not work so it becomes necessary to replace the “other” whitespace characters
   with character 32. Then LTRIM and
   RTRIM will work as expected.
   
   I created this simple UDF to cleanup the data when necessary. I only use this when
   troubleshooting an old SQL 2000 application or pinpointing weird data coming into
   the Data Warehouse from the ERP.
   
   Here is a link to the Code Project article

   /*
   SQL 2000 Version
   2/2/2012 CValenzuela
   http://www.codeproject.com/Tips/330787/LTRIM-RTRIM-doesn-t-always-work
   UDF to really trim the white spaces. 
   When users copy and paste from word, excel, or some other application
   into a text box the special non printing whitespace characters 
   like a line feed remain. This will replace all the non printing
   whitespace characters with Character 32 which is the space bar then
   perform an LTRIM and RTRIM
    
   Declare 
   @Seed as varchar(20),
   @Test as varchar(50)
    
   Set @Seed= ' CValenzuela';
   Set @Test =  CHAR(0)+CHAR(9)+CHAR(10)+CHAR(11)+CHAR(12)+CHAR(13)+CHAR(14)+CHAR(160)
       + @Seed + CHAR(0)+CHAR(9)+CHAR(10)+CHAR(11)+CHAR(12)+CHAR(13)+CHAR(14)+CHAR(160)
    
   Select
   	@Seed as Seed,
   	LTRIM(RTRIM(@SEED)) as Seed_Trimmed,	
   	@Test as Test,
   	LTRIM(RTRIM(@Test)) as Test_Trimmed,
   	dbo.udfTrim(@Test) as Test_Trimmed2,
   	
   	Len(@Seed) as Seed_Length,
   	DataLength(@Seed) as Seed_DataLength,
   	LEN(LTRIM(RTRIM(@Seed))) as Seed_Trimmed_Length,
   	DataLength(LTRIM(RTRIM(@Seed))) as Seed_Trimmed_DataLength,
    
   	Len(@Test) as Test_Length,	
   	LEN(LTRIM(RTRIM(@TEST))) as Test_Trimmed_Length,    	
   	DataLength(LTRIM(RTRIM(@TEST))) as Test_Trimmed_DataLength,    	
   	LEN(dbo.udfTrim(@Test)) as Test_UDFTrimmed_Length,
   	DataLength(dbo.udfTrim(@Test)) as Test_UDFTrimmed_DataLength 
   */
   CREATE FUNCTION [dbo].[udfTrim] 
   (
       @StringToClean as varchar(8000)
   )
   RETURNS varchar(8000)
   AS
   BEGIN
       --Replace all non printing whitespace characers with Characer 32 whitespace
       --NULL
       Set @StringToClean = Replace(@StringToClean,CHAR(0),CHAR(32));
       --Horizontal Tab
       Set @StringToClean = Replace(@StringToClean,CHAR(9),CHAR(32));
       --Line Feed
       Set @StringToClean = Replace(@StringToClean,CHAR(10),CHAR(32));
       --Vertical Tab
       Set @StringToClean = Replace(@StringToClean,CHAR(11),CHAR(32));
       --Form Feed
       Set @StringToClean = Replace(@StringToClean,CHAR(12),CHAR(32));
       --Carriage Return
       Set @StringToClean = Replace(@StringToClean,CHAR(13),CHAR(32));
       --Column Break
       Set @StringToClean = Replace(@StringToClean,CHAR(14),CHAR(32));
   	--Non-breaking space
       Set @StringToClean = Replace(@StringToClean,CHAR(160),CHAR(32));
       
       Set @StringToClean = LTRIM(RTRIM(@StringToClean));
       Return @StringToClean
   END
   GO