取出数字
IF OBJECT_ID(’DBOGET_NUMBER’) IS NOT NULL
DROP FUNCTION DBOGET_NUMBER
GO
CREATE FUNCTION DBOGET_NUMBER(@S VARCHAR())
RETURNS VARCHAR()
AS
BEGIN
WHILE PATINDEX(’%[^]%’@S) >
BEGIN
set @s=stuff(@spatindex(’%[^]%’@s)’’)
END
RETURN @S
END
GO
测试
PRINT DBOGET_NUMBER(’呵呵ABCABC’)
GO
取出英文
IF OBJECT_ID(’DBOGET_STR’) IS NOT NULL
DROP FUNCTION DBOGET_STR
GO
CREATE FUNCTION DBOGET_STR(@S VARCHAR())
RETURNS VARCHAR()
AS
BEGIN
WHILE PATINDEX(’%[^az]%’@S) >
BEGIN
set @s=stuff(@spatindex(’%[^az]%’@s)’’)
END
RETURN @S
END
GO
测试
PRINT DBOGET_STR(’呵呵ABCABC’)
GO
取出中文
IF OBJECT_ID(’DBOCHINA_STR’) IS NOT NULL
DROP FUNCTION DBOCHINA_STR
GO
CREATE FUNCTION DBOCHINA_STR(@S NVARCHAR())
RETURNS VARCHAR()
AS
BEGIN
WHILE PATINDEX(’%[^吖座]%’@S) >
SET @S = STUFF(@SPATINDEX(’%[^吖座]%’@S)N’’)
RETURN @S
END
GO
PRINT DBOCHINA_STR(’呵呵ABCABC’)
GO
SELECT * FROM (select ’ASDKG论坛K联通DL’ as col)TB WHERE COL LIKE N’%[吖咗]%’
