字符串处理问题

Question

如何在一段字符串中取出所有的英文字符，如下例：
周杰(董事长)zhoujie
孙红雷(总经理)sunhonglei
……
取出完字符后，如下结果
周杰(董事长)    zhoujie
孙红雷(总经理)    sunhonglei
……
就是将字符串中的英文字符单独取出，希望各位大大不吝赐教，感谢！

Answer 1

IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO

Answer 2

参考：
http://blog.csdn.net/maco_wang/article/details/6260197

Answer 3

IF OBJECT_ID('dbo.fn_eng_word') IS NOT NULL
DROP FUNCTION dbo.fn_eng_word
GO
CREATE FUNCTION dbo.fn_eng_word(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[a-z|A-Z]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[a-z|A-Z]%',@S),1,N'')
RETURN @S
END
GOif object_id('test') is not null drop table test
go
create table test([name] nvarchar(30))
go
insert into test
select N'周杰(董事长)zhoujie' union all
select N'孙红雷(总经理)sunhonglei'
goselect dbo.fn_eng_word([name]) f1,replace([name],dbo.fn_eng_word([name]),'') f2 from test/*(2 row(s) affected)
f1                                                                                                   f2
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
周杰(董事长)                                                                                              zhoujie
孙红雷(总经理)                                                                                             sunhonglei(2 row(s) affected)
*/

Answer 4

declare @str nvarchar(100)
set @str = '周杰(董事长)zhoujie'select left(@str,patindex('%[a-zA-Z]%',@str)-1) [1],
right(@str,len(@str)-patindex('%[a-zA-Z]%',@str)) [2]/*********************1                                                                                                    2
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
周杰(董事长)                                                                                              houjie(1 行受影响)