Scott Elkin

I had the need to today to alphabetize a phrase using SQL. For example, I needed “red cowboy pretty hat” to read “cowboy hat pretty red”. Here is the code I wrote to do so:

declare @input varchar(100)

set @input = "red cowboy pretty hat"

--declarations
DECLARE @i            int
        ,@len        int
        ,@word        varchar(100)
        ,@char        varchar(1)
        ,@newphrase    varchar(1000)

--Variable/Constant initializations
SET @i = 1
SET @len = LEN(@input)
SET @word = ""
SET @newphrase = ""

create table #temp(word varchar(100))

WHILE @i < @len
  begin

    SET @char = SUBSTRING(@input, @i, 1)
    IF @char = " "
      begin
            --add word to temp table
            if len(@word) > 0
                insert into #temp(word)
                select @word

            SET @word = ""
      end
    else
        SET @word = @word + @char

    SET @i = @i + 1
  end

select @newphrase = @newphrase + " " + word
from #temp
order by word

print @newphrase

drop table #temp

We all know we shouldn’t have duplicates in the database. And despite my best efforts, somehow they sneak in from some legacy code, or from the hyper-active-compulsory-submit-the-form-fifty-times-in-five-seconds-bloke.

So now and then I find myself writing the same SQL to track them down. Most solutions online say that you have to use a temporary table or rename tables to get rid of them. This solution works without either, providing my way of eliminating the duplicates is sufficient for your needs.

Here is my code to find duplicates:

Wow, that’s a mouthful. This is what I am trying to convey: When you do a Group By with Rollup, what happens is that the rolled up column shows null, and won’t necessarily be ordered at the bottom. For example:

select
    firstname
    , count(*) as total
from AdsEntered ads
join tblStaff s on s.staffid = ads.staffidgroup by firstname
with rollup
order by
total desc

This would output:

Now, I want the person with the highest total at the top, but I certainly don’t want the the total row being first. So the goal is to still sort descending, yet stick the total row at the bottom:

I don't know what my problem is with my short term memory, but certain things defy my ability to remember. Which way do I turn the faucet? Did I pass my street to the barbershop? What the heck DID I do last weekend?

In all seriousness, I was posed that last question one week after my wedding and was unable to answer for at least a minute. Oy yoy yoy.

One piece of SQL lore I take for granted is COALESCE(). The dictionary definition describes SQL's use pretty close as “To bring or come together into a united whole”. Books Online states in laymans terms that SQL allows you to pass any number of arguments to the function, and it will return the first non-null one back to you. In other words, it “brings together” many variables, and outputs just one.

For example, if a user wants to search for a record in our Customers table, they may want to search for Firstname, Lastname, Email, Phone, and Address. How do you write your SQL for this?

Never had a good reason to do this up until now, but if you need to get a value from a generated sql string, there are a few hoops you must jump through. Example:

declare @table 		varchar(20) 	,@username 	varchar(30)

--can't use double quotes w/out this  SET QUOTED_IDENTIFIER OFF

--set your variablesselect @table = 'tblcustomers'	,@username = 'webmaster'

--create a temp table to hold your valuecreate table #temp (field varchar(50))

--insert the value into temp tableinsert into #tempexec ('select email from ' + @table + ' where username = "' + @username + '"')

--voilaselect field from #temp

drop table #temp

Reference: Getting Creative with EXEC and EXEC()

This is a rad sproc to find dependencies from Brett. Sometimes I come across a sproc that I *think* I can delete but you never can be too sure. This makes that process A LOT easier. I am reposting it here for my own benefit with some slight formatting differences to make it easier for me to read:

/* testing...

exec usp_depends2 ‘tblCustomers’

*/
CREATE procedure usp_depends2
@objname nvarchar(776) –the object we want to check

as

declare @objid int –the id of the object we want
,@found_some bit –flag for dependencies found
,@dbname sysname

Question: How do you count distinct rows in a query?

1st Try: I *thought* this would work:

select distinct count(taskid) from #temp

however, it returned the count of all taskid's.

2nd Try: then I tried keeping duplicates out to begin with, so I could just do a normal count(taskid). That wasn't fool proof.

Solution: Just out of the “what-would-happen-if-I-did-this” department:

select count(distinct taskid) from #temp

Looks totaly wrong, but it worked!??! Althought I have never heard or seen this done, I am sure people will show me up by telling me how it is “all over Books Online” and such. Whatever, for now, I “invented” it.

I have said it before, but I use this website as a storage facility for my brain. Trying to find that one useful script I used 2 years ago is always harder than it needs to.

Well, here is the latest cool script I couldn't find. It was written by Narayana Vyas Kondreddi and is immensely useful.