Remove Spaces and Non Alpahnumeric Characters

SELECTRTRIM(CUSTNMBR)AS CUSTNMBR
,REPLACE(REPLACE(CUSTNMBR ,SUBSTRING(CUSTNMBR ,PATINDEX('%[^a-zA-Z0-9 ]%' ,CUSTNMBR) ,1) ,'') ,CHAR(32) ,'')AS NU_CUSTNMBR

,CUSTNAME


FROM RECORDSTABLE


WHERECUSTNMBR LIKE '%[^a-zA-Z0-9 ]%'

OR CHARINDEX(CHAR(32) ,RTRIM(CUSTNMBR)) > 0;

Excellent Query Analysis Tool

I'm always looking for new tools for SQL. 

Here is one that truly sets the standard for Query Execution Plan Analysis:

SQL SENTRY PLAN EXPLORER

The feature I like about it this best is that you can navigate your sql code and the analysis plan window stays in sync with where you are in the code.  Conversely, the code window is sync'd to the analysis plan. 

On top of this, the tool also provides tabular performance metrics of the various operations instead of the user having to hunt through the diagrams to find the problems. 

Best of all, there is a free version you can check out and decide if you want to get the reasonably priced pro-version.

There is too much in this tool for me to list all the great points about it.  I realize this sounds like a sales pitch, but after you check it out you will be impressed too.

SMS Tips and Tricks SQL

I don't often link to other posts - Yeah I know, a fatal bloggers mistake.  However, here is a a really nice set of tips: 

http://www.bidn.com/blogs/MMilligan/bidn-blog/3326/sql-server-management-studio-ssms-tips-and-tricks

Single Quotes in String Values

The following code includes an example of building a string value which has a single quote character in the text and also searching a field for a single quote character.

declare @test varchar(1000)

declare @i int

IF OBJECT_ID('tempdb..#testtable') IS NOT NULL  

    DROP TABLE #testtable

create table #testtable

(val varchar(200)

)

-- Intentionally misspelled

set @i = 1

set @test =  'Had this been a real emergency, you''re keester would already by fried'

 

while @i < 20

begin

  insert #testtable

  select cast(@i as varchar(1000))

  set @i = @i + 1

end

insert #testtable

select @test

-- find the one record 

select val

from #testtable

where val like '%''%'

Find instances of a string in all your stored procedures:

This is a great little trick I came across while we were moving from one server to another and had to change the prefix on all references to that SQL machine - but it work in any instance where you need to search through all your stored code:

USE <databasename>; go   SELECT routine_name               ,routine_definition FROM information_schema.routines WHERE routine_definition LIKE '%<my search string>%'   AND routine_type = 'PROCEDURE' ORDER BY routine_name;

SSRS - List User Parameter Selection

In SSRS,
How do I list the parameter the user selected in the parameter drop down?

Place a label on the header of your report and create an expression for the value:

 = "Days Selected " + Parameters!parm_Days_Past_Due.Label



What if multi-select is enabled for the parameter and the've chosen more than one value?
 ="Location Type= " + Join(Parameters!Location_Type.Label,",")

In this case, the join function treats the drop down label as an array object and concantonates all the values separated by a comma.  You can also use other characters to separate the list, but a comma is the most easily understood.

Worst Code Tester Ever!!!!

This one speaks for itself.............