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

问题描述

我试图连接 2 列，中间有一个空格，但出现整理错误:

SELECT DISTINCTp.PERSON_ID,p.ID_NUMBER，CONCAT(p.FULLNAMES, CONCAT(' ', p.SURNAME)) 作为名字，o.ORG_NAME，w.WARD_DESCRIPTION 作为病房，ess.DESCRIPTION 作为部门

<块引用>

无法解决concat操作中SQL_Latin1_General_CP1_CI_AS"和Latin1_General_CI_AS"之间的排序冲突

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

然后我试图将空白整理到这个整理中，但我不知道如何做到这一点.我的尝试:

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

什么的?

解决方案

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

SELECT DISTINCTCONCAT(p.FULLNAMES COLLATE Latin1_General_CI_AS，(CONCAT(' ' COLLATE Latin1_General_CI_AS,p.SURNAME COLLATE Latin1_General_CI_AS))) AS NAME发件人 p

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

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

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,

or something?

解决方案

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屋！