有一些记录在某个字段里,能不能设计出一个函数只取到字段内容中的汉字,
比方ID Content
1 abcd测试efg
2 zzzz大家好yyy
3 谢谢test
select id,dbo.hanzi(content) from table 最终结果是:1 测试
2 大家好
3 谢谢
比方ID Content
1 abcd测试efg
2 zzzz大家好yyy
3 谢谢test
select id,dbo.hanzi(content) from table 最终结果是:1 测试
2 大家好
3 谢谢
create function getnewstr1(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^0-9]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
--建立如下函数(非吖-做,之外的字符删除)
go
create function getnewstr2(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^吖-做]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
这是上次我看到CSDN里面一个牛人写的,我忘记是谁了⊙﹏⊙b汗
create function get_china(@s varchar(50))
returns varchar(50)
as
begin
declare @i int
set @i=1
while patindex('%[^吖-座]%',@s) > 0 and @i<=len(@s)
begin
if substring(@s,@i,1) not like '%[吖-座]%'
set @s=stuff(@s,@i,1,'')
else
set @i=@i+1
end
return @s
endif object_id('tb') is not null
drop table tb
go
create table tb(name varchar(1000))
insert into tb select 'asdas啊das'
insert into tb select 'asda爱是sdas'
insert into tb select '1423啊124'
insert into tb select 'asdsa123阿斯顿4r13'
insert into tb select '撒旦阿斯顿123座位41'
select dbo.get_china(name) as name from tb
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-22 17:16:19
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID int,Content varchar(13))
INSERT INTO @tb
SELECT 1,'abcd测试efg' UNION ALL
SELECT 2,'zzzz大家好yyy' UNION ALL
SELECT 3,'谢谢test'--SQL查询如下:SELECT *
FROM @tb AS A
OUTER APPLY (SELECT x=(SELECT SUBSTRING(A.Content,number,1) AS [text()]
FROM master.dbo.spt_values
WHERE type = 'p' AND SUBSTRING(A.Content,number,1)<>''
AND SUBSTRING(A.Content,number,1) LIKE '%[吖-做]%'
FOR XML PATH(''),TYPE).value('.','varchar(500)')) AS B/*
ID Content x
----------- ------------- ---------------------------
1 abcd测试efg 测试
2 zzzz大家好yyy 大家好
3 谢谢test 谢谢(3 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-22 17:14:36
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Content] varchar(13))
insert [tb]
select 1,'abcd测试efg' union all
select 2,'zzzz大家好yyy' union all
select 3,'谢谢test'
--------------开始查询--------------------------
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
select id,[Content]=DBO.CHINA_STR([Content]) from tb
GO----------------结果----------------------------
/* ----------- ----------------------------------------------------------------------------------------------------
1 测试
2 大家好
3 谢谢(3 行受影响)*/
if object_id('[s]') is not null drop table [s]
create table [s] (ID int,Content varchar(13))
insert into [s]
select 1,'abcd测试efg' union all
select 2,'zzzz家好yy大y' union all
select 3,'谢谢test'
go
create function hanzi(@text varchar(100))
returns varchar(100)
as
begin
declare @new varchar(1500)
while(patindex('%[吖-咗]%',@text)>0)
begin
set @new=isnull(@new,'')+substring(@text,patindex('%[吖-咗]%',@text),1)
set @text=substring(@text,patindex('%[吖-咗]%',@text)+1,len(@text))
end
return @new
end
go
select content=dbo.hanzi(content) from [s]
create function hanzi(@s varchar(100))
returns varchar(100)
as
begin
declare @str varchar(100)
while (patindex('%[^吖-咗]%',left(ltrim(@s),1))>0)
begin
set @s=stuff(@s,1,patindex('%[吖-咗]%',@s)-1,'')
set @str=isnull(@str,'')+isnull(left(@s,patindex('%[^吖-咗]%',@s)-1),'')
set @s=stuff(@s,1,patindex('%[^吖-咗]%',@s)-1,'')
end
return @str
end
goselect dbo.hanzi('abcd测试efg通过xyz')
/**----------------------------------------------------------------------------------------------------
测试通过(1 行受影响)
**/
create function hanzi(@s varchar(100))
returns varchar(100)
as
begin
declare @str varchar(100)
set @s=@s+'s'
while (patindex('%[^吖-咗]%',left(ltrim(@s),1))>0)
begin
set @s=stuff(@s,1,patindex('%[吖-咗]%',@s)-1,'')
set @str=isnull(@str,'')+isnull(left(@s,patindex('%[^吖-咗]%',@s)-1),'')
set @s=stuff(@s,1,patindex('%[^吖-咗]%',@s)-1,'')
end
return @str
end
go
set @s='呵中並丧呵ab䈩c座鱇咘咗쥅䶮123abc'
while patindex('%[^吖-座]%',@s) > 0
set @s = stuff(@s,patindex('%[^吖-座]%',@s),1,N'')
select @s,len(@s)
/*
呵中並丧呵座鱇咘 8
*/declare @s nvarchar(2000)
set @s='呵中並丧呵ab䈩c座鱇咘咗쥅䶮123abc'
while patindex('%[^吖-咗]%',@s) > 0
set @s = stuff(@s,patindex('%[^吖-咗]%',@s),1,N'')
select @s,len(@s)
/*
呵中並丧呵座鱇咘咗 9
*/肉眼结果:
/*
呵中並丧呵䈩座鱇咘咗䶮 11
*/
/************************************************/
/*<summary>取出字符串中的汉字</summary>**********/
/*function:m_getchinese**************************/
/*author:maco_wang*******************************/
/*createtime:2008-11-19**************************/
/*<param name="@str">输入的字符串</param>****/
/************************************************/ create function [dbo].[m_getchinese]
(
@chinese nvarchar(100)
)
returns varchar(100)
as
begin
--begin while
while patindex('%[^吖-咗]%',@chinese) > 0
begin
set @chinese = stuff(@chinese,patindex('%[^吖-咗]%',@chinese),1,N'');
end
--end while
return @chinese
enddeclare @table table (ID int,Content varchar(13))
insert into @table
select 1,'abcd测试efg' union all
select 2,'zzzz大家好yyy' union all
select 3,'谢谢test'select ID,dbo.m_getchinese(Content) as Content from @table/*
ID Content
----------- ----------
1 测试
2 大家好
3 谢谢
*/
returns nvarchar(1000)
as
begin
declare @strall nvarchar(1000)
set @strall=''
declare @inti int
set @inti=0
declare @strTemp nvarchar(100)
set @strTemp=''
while(@inti<=len(@str)-1)
begin
set @strTemp=substring(@str,@inti,1)
if @strTemp like '%[吖-做]%'
set @strall=@strall+@strTemp
set @inti=@inti+1
end
return @strallend
select dbo.getChina('as阿得发抖d')