Collation sequence in query of query

[tystadb]

Sorting a list of names which may contain apostrophes produces a different
sequence of the results between SQL Server and query of query. For example,
sorting Oliver, O'Toole, Ouch in SQL Server results in O'Toole, Oliver, Ouch
when sorted from a query of a query. Does anybody know any way around this
problem?

[paross1]

Perhaps you could select an additional column in your original query, where you
strip the single quotes from the string using the SQL REPLACE() function, then
order on this additonal column in your Q-of-Q (you really don't need to ever
display it in your output)

For example,

<cfquery name ="Q1" datasource ="DSN">
SELECT name, REPLACE(name, CHAR(39), '') AS sort_col
FROM yourtable
WHERE whatever
</cfquery>

then in your Q-of-Q

<cfquery name="yourQofQ" dbtype="query">
SELECT *
FROM Q1
WHERE whatever
ORDER BY sort_col
</cfquery>

There are probably easier ways to do this, but this is just what happened to
pop into my head.

Phil