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 '%''%'