数据是“lastName，FirstName”。我需要把它写成“FirstName LastName”

Running sql server 2008 The title says it, but in my select statement I have this

运行sql server 2008标题说明了,但在我的select语句中我有这个

COALESCE( ca.AttributeList.value('(/AttributeList/IRName)[1]','varchar(max)')
        ,ca2.AttributeList.value('(/AttributeList/IRName)[1]','varchar(max)'))
      AS IR_Name

and it returns lastName, FirstName

它返回lastName,FirstName

This becomes a problem when exporting to a csv as it creates two separate columns and what not.

导出到csv时会出现问题,因为它会创建两个单独的列,而不是。

I somehow have to figure out how to have the string be firstName lastName, no commas, and putting the firstName first.

我不得不弄清楚如何让字符串成为firstName lastName,没有逗号,并将firstName放在第一位。

2 个解决方案

#1

    SELECT PARSENAME(REPLACE('John , Doe', ',', '.'), 1) + ' ' + PARSENAME(REPLACE('John , Doe', ',', '.'), 2)

this will switch 'John , Doe' to 'Doe John'. Now, you just need replace the 'John, Doe" with the

这会将'John,Doe'改为'Doe John'。现在,你只需要用'替换'John,Doe'

   COALESCE(ca.AttributeList.value('(/AttributeList/IRName)[1]','varchar(max)'),ca2.AttributeList.value('(/AttributeList/IRName)[1]','varchar(max)')) AS IR_Name

#2

Another way to do this is by using string functions:

另一种方法是使用字符串函数:

select (case when IR_Name like '%,%'
             then (ltrim(rtrim(substring(IR_NAME, charindex(',', IR_NAME)+1, 1000))) + ' ' +
                   ltrim(rtrim(left(IR_Name, charindex(',', IR_NAME) - 1)))
                  )
             else IR_NAME
         end) t
from (select COALESCE( ca.AttributeList.value('(/AttributeList/IRName)[1]','varchar(max)')
                      ,ca2.AttributeList.value('(/AttributeList/IRName)[1]','varchar(max)'))
             AS IR_Name
      . . .
     ) t

The trimming functions are to remove extra spaces.

修剪功能是删除多余的空间。

The parsename solution is clever. But, it looks strange since parsename is designed for multipart naming conventions, with up to four parts separated by periods.

parsename解决方案很聪明。但是,它看起来很奇怪,因为parsename是为多部分命名约定而设计的,最多有四个部分由句点分隔。

#1

    SELECT PARSENAME(REPLACE('John , Doe', ',', '.'), 1) + ' ' + PARSENAME(REPLACE('John , Doe', ',', '.'), 2)