- 1. 查询字符串中是否包含非数字字符
- SELECT PATINDEX('%[^0-9]%', '1235X461')
- SELECT PATINDEX('%[^0-9]%', '12350461')
- 2. 查询字符串中是否包含数字字符
- SELECT PATINDEX('%[0-9]%', 'SUYLLGoO')
- SELECT PATINDEX('%[0-9]%', 'SUYLLG0O')
- 3.函数判断字符串只包含数字
- CREATE FUNCTION [dbo].fn_IsNumeric
- (
- @pString VARCHAR(8000)
- )
- RETURNS bit
- WITH ENCRYPTION
- AS
- BEGIN
- DECLARE @vJudge int
- SET @vJudge = 0
- SELECT @vJudge =
- CASE
- WHEN PATINDEX('%[0-9]%', LOWER(@pString)) > 0 THEN 0
- WHEN PATINDEX('%[0-9]%', LOWER(@pString)) = 0 THEN 1
- END
- RETURN @vJudge
- END
- 4.函数判断字符串只包含字母(忽略大小写)
- CREATE FUNCTION [dbo].fn_IsAlpha
- (
- @pString VARCHAR(8000)
- )
- RETURNS bit
- WITH ENCRYPTION
- AS
- BEGIN
- DECLARE @vJudge int
- SET @vJudge = 0
- SELECT @vJudge =
- CASE
- WHEN PATINDEX('%[a-z]%', LOWER(@pString)) > 0 THEN 0
- WHEN PATINDEX('%[a-z]%', LOWER(@pString)) = 0 THEN 1
- END
- RETURN @vJudge
- END
- --该片段来自于http://www.codesnippet.cn/detail/111120137016.html
来源: http://www.codesnippet.cn/detail/111120137016.html