34,593
社区成员
发帖
与我相关
我的任务
分享
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 行受影响)
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
GO
if 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'
go
select 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)
*/
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