1

I have 2 names Georg Reisse Bauunternehmung and Georg Reiße Bauunternehmung and need to insert both into a table. I am using a group by to eliminate duplicate rows in the new table. Since the ß is being tranlated to ss I am only seeing one name and the ß is be converted to a ?. Any ideas on how to have both names one with a ? and one with ss inserted to the table?

Inital table data:

3449839 Georg Reisse Bauunternehmung Formerly Known As

3449839 Georg Reiße Bauunternehmung Formerly Known As

Data returned from query

1 NULL NULL Georg Rei?e Bauunternehmung 2017-01-18 09:46:22.997 Formerly Known As

Query:

SELECT
    1 AS Enhancement_id, 
    CASE 
        When EntityName is null and not (Surname is null or Surname = '') and (MiddleName is null or MiddleName = '') Then Cast(FirstName as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS
        When EntityName is null and not (Surname is null or Surname = '') Then Cast(FirstName as varchar(max)) + ' ' + Cast(MiddleName as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS
        Else null
    END AS FirstName,
    CASE
        When EntityName is null and not (Surname is null or Surname = '') Then Cast(Surname AS varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS
        When EntityName is null and (Surname is null or Surname = '') Then Cast(FirstName AS varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS
        Else null
    END AS LastName,
    CASE
        When not EntityName is null Then Cast(EntityName AS varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS 
        Else null
    END AS OtherName,
    GETDATE() AS SourceDate,
    NameType
FROM
    [dbo].[Name]
WHERE 
    NameType <> 'Spelling Variation'
GROUP By
    EntityID, EntityName, FirstName, Surname, MiddleName, NameType
ORDER BY OtherName
Hu Anderson
  • 11
  • 1
  • 1
    I have linked to a duplicate which contains the solution to your problem. In a nutshell: Create an inner query which selects your data *with the desired collation*, then GROUP BY in the outer query. – Heinzi Jan 18 '17 at 14:54
  • 1
    Note that OP already has a case sensitive collation in the query. – Wagner DosAnjos Jan 18 '17 at 15:37

0 Answers0