–SQL 判斷字段值是否有中文
create function fun_getCN(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@CN nvarchar(4000)
set @CN=”
while len(@str)>0
begin
set @word=left(@str,1)
if unicode(@word) between 19968 and 19968 20901
set @CN=@CN @word
set @str=right(@str,len(@str)-1)
end
return @CN
end
select dbo.fun_getCN(‘ASDKG論壇KDL’)
–論壇
select dbo.fun_getCN(‘ASDKG論壇KDL’)
–論壇
select dbo.fun_getCN(‘ASDKDL’)
–空
[c-sharp] view plaincopy
–SQL 判斷字段值是否有中文
create function fun_getCN(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@CN nvarchar(4000)
set @CN=”
while len(@str)>0
begin
set @word=left(@str,1)
if unicode(@word) between 19968 and 19968 20901
set @CN=@CN @word
set @str=right(@str,len(@str)-1)
end
return @CN
end
select dbo.fun_getCN(‘ASDKG論壇KDL’)
–論壇
select dbo.fun_getCN(‘ASDKG論壇KDL’)
–論壇
select dbo.fun_getCN(‘ASDKDL’)
–空

————————————-
–提取數(shù)字
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

——————————————————————–
–過濾重復(fù)字符
IF OBJECT_ID(‘DBO.DISTINCT_STR’) IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT @S
IF RIGHT(@S,1)<>@SPLIT
SET @S = @S @SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
SET @INDEX = CHARINDEX(@SPLIT,@S)
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX LEN(@SPLIT)))
IF @NEW IS NULL
SET @NEW = ISNULL(@NEW,”) @TEMP
ELSE
SET @NEW = ISNULL(@NEW,”) REPLACE(@TEMP,@SPLIT,”) @SPLIT
WHILE CHARINDEX(@TEMP,@S)>0
BEGIN
SET @S=STUFF(@S,CHARINDEX(@TEMP,@S) LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S) LEN(@SPLIT))-CHARINDEX(@TEMP,@S),”)
END
END
RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.DISTINCT_STR(‘A,A,B,C,C,B,C,’,',’)
–A,B,C
GO

——————————————————————–
–過濾重復(fù)字符2
IF OBJECT_ID(‘DBO.DISTINCT_STR2′) IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR2
GO
CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
WHILE LEN(@S)>0
BEGIN
SET @NEW=ISNULL(@NEW,”) LEFT(@S,1)
SET @S=REPLACE(@S,LEFT(@S,1),”)
END
RETURN @NEW
END
GO
SELECT DBO.DISTINCT_STR2(‘AABCCD’)
–ABCD
GO

——————————————————————–
IF OBJECT_ID(‘DBO.SPLIT_STR’) IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar(8000), –包含多個數(shù)據(jù)項的字符串
@INDEX int, –要獲取的數(shù)據(jù)項的位置
@SPLIT varchar(10) –數(shù)據(jù)分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN=LEN(@SPLIT ’A')-2
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S @SPLIT)>0
SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S @SPLIT) @SPLITLEN,”)
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S @SPLIT)-1),”))
END
GO
PRINT DBO.SPLIT_STR(‘AA|BB|CC’,2,’|')
–