怎样把Name 分开中英文
NAME
CHUN ON INVESTMENT COMPANY LIMITED (全安投資有限公司)
PO ON MARINE & FIRE INSURANCE AND GODOWN COMPANY LIMITED -THE-
DAIRY FARM COMPANY, LIMITED -THE- 牛奶有限公司
TUNG ON INVESTMENT COMPANY, LIMITED 同安置業有限公司
WING ON COMPANY LIMITED -THE- 永安有限公司
China Construction Bank (Asia) Corporation Limited 中國建設銀行(亞洲)股份有限公司
数字据字段内容是这样的。怎样可以分开。。英文与中文分开
select Name form a
[解决办法]
WITH a1 (NAME) AS
(
SELECT 'NAME CHUN ON INVESTMENT COMPANY LIMITED (全安投资有限公司)' UNION all
SELECT 'PO ON MARINE & FIRE INSURANCE AND GODOWN COMPANY LIMITED -THE- DAIRY FARM COMPANY, LIMITED -THE- 牛奶有限公司' UNION all
SELECT 'TUNG ON INVESTMENT COMPANY, LIMITED 同安置业有限公司' UNION all
SELECT 'WING ON COMPANY LIMITED -THE- 永安有限公司' UNION all
SELECT 'China Construction Bank (Asia) Corporation Limited 中国建设银行(亚洲)股份有限公司'
)
,a2 AS
(
SELECT a.name,MIN(b.number) number
FROM a1 a,master.dbo.spt_values b
WHERE CASE WHEN UNICODE(SUBSTRING(a.name,b.number,1))>255 OR UNICODE(SUBSTRING(a.name,b.number,1))<0 THEN b.number ELSE 0 END>0
GROUP BY a.name
)
,a3 AS
(
SELECT name,LEFT(name,number-1) eName,right(name,LEN(name)-number+1) cName
FROM a2
)
SELECT CASE WHEN RIGHT(eName,1)='(' THEN LEFT(eName,LEN(ename)-1) ELSE ename END ename
,CASE WHEN left(cName,1)=')' THEN right(cName,LEN(cname)-1) ELSE cname END cname
FROM a3
[解决办法]
WITH a1 (NAME) AS
(
SELECT 'NAME CHUN ON INVESTMENT COMPANY LIMITED (全安投資有限公司)' UNION all
SELECT 'PO ON MARINE & FIRE INSURANCE AND GODOWN COMPANY LIMITED -THE- DAIRY FARM COMPANY, LIMITED -THE- 牛奶有限公司' UNION all
SELECT 'TUNG ON INVESTMENT COMPANY, LIMITED 同安置業有限公司' UNION all
SELECT 'WING ON COMPANY LIMITED -THE- 永安有限公司' UNION all
SELECT 'China Construction Bank (Asia) Corporation Limited 中國建設銀行(亞洲)股份有限公司'
)
,a2 AS
(
SELECT a.name,MIN(b.number) number
FROM a1 a,master.dbo.spt_values b
WHERE CASE WHEN UNICODE(SUBSTRING(a.name,b.number,1))>255 OR UNICODE(SUBSTRING(a.name,b.number,1))<0 THEN b.number ELSE 0 END>0
GROUP BY a.name
)
,a3 AS
(
SELECT name,LEFT(name,number-1) eName,right(name,LEN(name)-number+1) cName
FROM a2
)
SELECT CASE WHEN RIGHT(eName,1)='(' THEN LEFT(eName,LEN(ename)-1) ELSE ename END ename
,CASE WHEN right(cName,1)=')' THEN left(cName,LEN(cname)-1) ELSE cname END cname
FROM a3
