34,588
社区成员
发帖
与我相关
我的任务
分享
/************************************************/
/*<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
end
declare @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 谢谢
*/
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)
/*
呵中並丧呵座鱇咘 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
*/
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
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
go
select dbo.hanzi('abcd测试efg通过xyz')
/**
----------------------------------------------------------------------------------------------------
测试通过
(1 行受影响)
**/
--> 测试数据: [s]
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]
----------------------------------------------------------------
-- 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 行受影响)
*/
-------------------------------------
-- 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 行受影响)
*/
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
end
if 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
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
select * from tb where PATINDEX('%[吖-做]%',Content)>0