And the results...

SQL 7.0 desktop db installation on a development machine.  Main table has 600k rows, keyword lookup using ~50 words.  Query run from ISQLW.

PATINDEX and the LIKE JOIN take identical...

Hi everyone, first off, thanks for all the feedback!  I'm glad to know that it works, it seemed like it should have to me.

1) Mark, Anton...Solved my problem.  I...

Thanks Frank,

It's as I feared.  I'll have to proceed with my dynamic sql method.  Too bad, I was already thinking of lots of uses for it if this was possible...

The...

IF you need the query to respond to new city additions I think you're stuck with writing dynamic SQL.  This involves either using a cursor or a temp table.  I...

quote:

---

...and its limit on character parameter length.

---

Yes, I ran into the parameter length issue with...

I thought I'd make the SQL handle MORE than 3 cases in case data changes in the future. It looks like as long as you know how many the...

I see now that I was close to it, but missed the MIN(pk) part so I was getting multiple results per FK, each with one less entry. Here's my...

Thanks, Jonathan.

Currently there are 0-3 possible results per FK, so I'm thinking I'll proceed using the self joins, I just have to figure out how to suppress...

THANK YOU! I can't even count how many times I've read that BOL subject and yet managed to miss that particular statement...ARRGH! At least I can stop beating...

DECLARE @tablecount

set @tablecount = (Select count(*) from authors)

--to see the results in Sql Query analyzer:

SELECT @tablecount

It's the parentesis that makes it work...

Hi Rheejay,

SET @siw = @curletr + CASE When LEN(@valctr) = 1 then '0' ELSE '' END + CAST(@valctr AS VARCHAR(2))

This line of SQL starts with your letter and concatenates a...

I'm guessing that Len's (5409045121009) solution would be better than mine since it's a set-based solution wheras mine uses a row by row approach. It would be interesting to...

Thanks ian!

That explains a lot of quirks I've experienced when attempting to concatenate values. And sure enough BOL has the list of precedence, now that I know what to...

Here's an example that you can paste into SQL query analyzer. I use this all the time, and have gotten away from the traditional cursors. Haven't ever used...

Nothing like replying to your own request! It turns out that I had to convert the result BACK to Varchar inside the CASE for it to work: