sql server 提取汉字、数字和字母的sql server方法

sql server 提取汉字/数字/字母的方法

--提取数字
if object_id('dbo.get_number2') is not null
drop function dbo.get_number2
go
create function dbo.get_number2(@s varchar(100))
returns varchar(100)
as
begin
while patindex('%[^0-9]%',@s) > 0
begin
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
end
return @s
end
go
--测试
print dbo.get_number('呵呵abc123abc')
go
--123
--------------------------------------------------------------------
--提取英文
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
--------------------------------------------------------------------
--提取中文

if object_id('dbo.china_str') is not null
drop function dbo.china_str
go
create function dbo.china_str(@s nvarchar(100))
returns varchar(100)
as
begin
while patindex('%[^吖-座]%',@s) > 0
set @s = stuff(@s,patindex('%[^吖-座]%',@s),1,n'')
return @s
end
go
print dbo.china_str('呵呵abc123abc')
go
 

select * from (select 'asdkg論壇k联通dl' as col)tb where col like n'%[吖-咗]%'

--使用函数

select 函数名(字段名) from 表名

select dbo.china_str(Contents) from M_News