SELECT CASE WHEN CHARINDEX(char(13)+char(10),REVERSE(COl))=1 THEN STUFF(Col,LEN(Col)-2,2,'') ELSE Col END AS Col FROM (SELECT CASE WHEN CHARINDEX(char(13)+char(10),COl)=1 THEN STUFF(Col,1,2,'') ELSE Col END AS Col FROM (SELECT RTRIM(LTRIM(Col)) AS COl FROM table1)t)t2
樓主看看,數據是否為 char(13)+char(10) char(10)--換行 char(13)--回車 SELECT CASE WHEN CHARINDEX(char(13),REVERSE(COl))=1 THEN STUFF(Col,LEN(Col)-1,1,'') ELSE Col END AS Col FROM ( SELECT CASE WHEN CHARINDEX(char(13),COl)=1 THEN STUFF(Col,1,1,'') ELSE Col END AS Col FROM (SELECT RTRIM(LTRIM(Col)) AS COl FROM table1)t )t2
--char(13)是回车,char(9)是换行,要去掉哪个自己选
--如果是去掉回车和首尾空格
select replace(rtrim(ltrim(col)),char(13),'') from tb
1.先消除2边的空格
2.把剩下的空格替换成'CHAR10'
3.把回车替换成空格
4.先消除2边的空格
5.把空格替换回车
6.把'CHAR10'替换成空格
CASE WHEN CHARINDEX(char(13)+char(10),REVERSE(COl))=1 THEN STUFF(Col,LEN(Col)-2,2,'') ELSE Col END AS Col
FROM
(SELECT CASE WHEN CHARINDEX(char(13)+char(10),COl)=1 THEN STUFF(Col,1,2,'') ELSE Col END AS Col FROM (SELECT RTRIM(LTRIM(Col)) AS COl FROM table1)t)t2
char(10)--換行
char(13)--回車
SELECT CASE WHEN CHARINDEX(char(13),REVERSE(COl))=1 THEN STUFF(Col,LEN(Col)-1,1,'') ELSE Col END AS Col
FROM
(
SELECT CASE WHEN CHARINDEX(char(13),COl)=1 THEN STUFF(Col,1,1,'') ELSE Col END AS Col FROM (SELECT RTRIM(LTRIM(Col)) AS COl FROM table1)t
)t2