StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><a href="https://stackoverflow.com/users/383167/will-a" title=" His profile">Will A</a>'s comment on my original post found the key - the collation. My posted query worked for me in the master database, too.</p> <p>Examining the collation suggested I was on the right track.</p> <pre><code>SELECT DATABASEPROPERTYEX('crm_mscrm', 'Collation') crmSQLCollation crmSQLCollation -------------------- Latin1_General_CI_AI (1 row(s) affected) SELECT DATABASEPROPERTYEX('master', 'Collation') masterSQLCollation masterSQLCollation ---------------------------- SQL_Latin1_General_CP1_CI_AS (1 row(s) affected) </code></pre> <p>Some frenzied searching later, I had this monstrosity of code, which </p> <ol> <li>explicitly specifies collation on each column,</li> <li>successfully executes, and </li> <li>returns the expected results</li> </ol> <p>To wit:</p> <pre><code>if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner; CREATE TABLE #tOwner(id int identity(1,1), email nvarchar(max) ); insert into #towner values ( cast('123@123.321' as nvarchar(max))); insert into #towner values ( cast('tsql rage' as nvarchar(max))); insert into #towner values ( cast('another@e.c' as nvarchar(max))); insert into #towner values ( cast('einstein.x.m' as nvarchar(max))); ;WITH data AS ( SELECT DISTINCT convert(nvarchar(max), email) datapoint FROM #tOwner ), CTE ( data_list, datapoint, length ) AS ( SELECT convert(nvarchar(max), '' ) Collate SQL_Latin1_General_CP1_CI_AS,convert(nvarchar(max), '' ) Collate SQL_Latin1_General_CP1_CI_AS, 0 UNION ALL SELECT convert(nvarchar(max),d.datapoint+';'+data_list) Collate SQL_Latin1_General_CP1_CI_AS,convert(nvarchar(max),d.datapoint) Collate SQL_Latin1_General_CP1_CI_AS, length + 1 FROM CTE c CROSS JOIN data d WHERE d.datapoint > c.datapoint ) SELECT D.data_list FROM ( SELECT data_list, RANK() OVER ( PARTITION BY 1 ORDER BY length DESC ) FROM CTE ) D ( data_list, rank ) WHERE rank = 1 ; if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner; </code></pre> <p>Sitting beautifully in my results window is the expected:</p> <pre><code>data_list ------------------------------------------------ tsql rage;einstein.x.m;another@e.c;123@123.321; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload