--【提取中文字符】
IF OBJECT_ID('dbo.fun_getCN') IS NOT NULL
DROP FUNCTION dbo.fun_getCN
GO
create function dbo.fun_getCN(@str varchar(4000))
returns varchar(4000)
as
begin
declare @word nchar(1),@CN varchar(4000)
set @CN=''
while len(@str)>0
begin
set @word=left(@str,1)
if unicode(@word) between 19968 and 40869
set @CN=@CN+@word
set @str=right(@str,len(@str)-1)
end
return @CN
end
GO
select dbo.fun_getCN('123我KK哈哈45')
--中国
select unicode('一')
select unicode('龥')
unicode中文编码范围:19968~40869
----------------------------------------------------------------
----------------------------------------------------------------
--【提取中文】
IF OBJECT_ID('DBO.get_Chinese') IS NOT NULL
DROP FUNCTION DBO.get_Chinese
GO
CREATE FUNCTION DBO.get_Chinese(@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 DBO.get_Chinese('123我KK哈哈45')
----------------------------------------------------------------
----------------------------------------------------------------
--【提取数字】
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
select dbo.GET_NUMBER2('123我KK哈哈45')
----------------------------------------------------------------
----------------------------------------------------------------
--【提取英文】
IF OBJECT_ID('DBO.get_English') IS NOT NULL
DROP FUNCTION DBO.get_English
GO
CREATE FUNCTION DBO.get_English(@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
SELECT DBO.get_English('123我KK哈哈45')
----------------------------------------------------------------
----------------------------------------------------------------
--【提取特殊字符】
DROP FUNCTION DBO.FN_GetSpecialCharacter
GO
ALTER FUNCTION DBO.FN_GetSpecialCharacter(@S VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[a-z0-9吖-座]%',@S) > 0
BEGIN
SET @s=stuff(@s,patindex('%[a-z0-9吖-座]%',@s),1,'')
END
RETURN @S
END
GO
SELECT DBO.FN_GetSpecialCharacter('123我KK$#&哈哈4^&5')
sqlserver提取中文_提取英文_提取数字(函数)
最新推荐文章于 2024-07-06 03:37:29 发布