在CONCAT中使用整理 [英] Use Collate in CONCAT

问题描述

我试图合并2列，中间有一个空格，但出现了校对错误：

I was trying to concatonate 2 columns with a whitespace in between and got a collation error:

SELECT DISTINCT
    p.PERSON_ID,
    p.ID_NUMBER,
    CONCAT(p.FULLNAMES, CONCAT(' ', p.SURNAME)) AS NAME,
    o.ORG_NAME,
    w.WARD_DESCRIPTION AS WARD,
    ess.DESCRIPTION AS SECTOR

不能解决concat操作中 SQL_Latin1_General_CP1_CI_AS和 Latin1_General_CI_AS之间的排序规则冲突

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the concat operation

数据库中两个违规列的排序规则是： Latin1_General_CI_AS

The collation of both the offending columns in my database is: Latin1_General_CI_AS

因此，我当时试图将空白归类为该归类，但是我不知道如何做这个。我的尝试：

So then I was trying to collate the whitespace to this collation, but I have no idea how to do this. My attempt:

CONCAT(p.FULLNAMES, (CONCAT((COLLATE Latin1_General_CI_AS = ' '), p.SURNAME))) AS NAME,

还是其他？

推荐答案

您将COLLATE放在每个字段之后，即在最坏的情况下：

You put the COLLATE after each field, viz in the worst case scenario:

SELECT DISTINCT
    CONCAT(p.FULLNAMES COLLATE Latin1_General_CI_AS, 
      (CONCAT(' ' COLLATE Latin1_General_CI_AS, 
          p.SURNAME COLLATE Latin1_General_CI_AS))) AS NAME
FROM Person p

这篇关于在CONCAT中使用整理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！