SQL Server 提取数字、提取英文、提取中文的sql语句

时间:2022-08-29 22:39:46

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

--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')

--空

 

-------------------------------------

--提取数字

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

 

--------------------------------------------------------------------

--过滤重复字符

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

 

--------------------------------------------------------------------

--过滤重复字符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),   --包含多个数据项的字符串

@INDEX int,       --要获取的数据项的位置

@SPLIT varchar(10)   --数据分隔符

)

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,'|')

--

GO