SQL 函数之奇思妙想 -- 自定义分离字符串和数字函数
三年不看一次书,一次看书看三年,三天不写一次代码,写一次代码写三天.
呵呵,开个玩笑,话说今天在一本书(SQL Cookbook )上看到了一个怪异的要求:
表中的数据混乱了(真奇葩),皆为以下这种前面是字符,后半是数字的情况.
FHDJKHJ3000
JKGLF434354
FHYDJ67889
要求是在查询的时候仅查每个值中的数字(或者说是字母,思路一样).
FHDJKHJ3000 --只要结尾的 3000 ,而不要前面的 FHDJKHJ
当我正感兴趣的时候,书中介绍的在几个数据库管理软件中实现的方式:
DB2 / Oracle / PostgreSQL 等里面用 TRANSLATE 函数 REPLACE 函数进行操作.
但是书中没有 SQL SERVER 中的实现方案,这让我很郁闷,难道我熟知的 SQL SERVER 不具备此功能?
我尝试了书中的方法,但 SQL SERVER 中貌似没有这两个函数(时势造博文,否则就不会有这篇口水文了...).
至少我的2008联机丛书和SERVER 2012 中查不到那两个函数,哪位大牛要知道类似的SQL SERVER 函数,还请告诉我.
于是,我就萌发了一个想法,能不能自己定义一个函数,实现从字符串中分离出结尾的数字呢?
于是我想弄个函数,函数中做的事情就是从传入的字符串中一次次的截取出来尝试转换为数字,成功则返回.
假设传入的是 'AB38' 则得到数字 38 ,它的原理为一次次截取并尝试转换,如 'AB38' 的匹配过程:
第一次截取 'AB38' 转换,结果肯定失败.
第二次截取 'B38' 转换失败.
第三次截取 '38' 转换成功,返回 38
我们的初衷是要获得传入字符串后面的数字.
没有图你说 J* 啊! 呵呵 这就附上我们的伦理图....
我们可以看出我们的步骤,不管字符串多长,我们都是这个步骤进行,从前往后依次排除,直到成功.
最坏情况我们得测试到最后才成功,最糟糕的情况是我们测试到最后还没成功...
于是我创建了函数:
-- 获得传入字符串后面的数字,并返回
CREATE FUNCTION MyConvertAndGetNumber(@string nvarchar(MAX))
RETURNS INT -- 返回值是 int 类型.
BEGIN
-- 参数长度,下标索引,返回数字
DECLARE @length int= len(@string),@index int = 1,@number int
-- 动态长度,切后的字符串
DECLARE @newLength int=@LENGTH,@newString nvarchar(max)WHILE(@INDEX <= @length)
BEGIN
SELECT @newString =SUBSTRING(@string,@index,@newLength)
SELECT @number = TRY_CONVERT(int,@newString) –-我们是尝试转换,不成功得到 null
IF(@number >=0) -- @number 可能为 null,但 null >= 0 永远为假,既必须转换成功条件才为真
BEGIN
RETURN @NUMBER
END
SELECT @newLength=@length-@index
SELECT @INDEX=@INDEX+1
END
RETURN @NUMBER
END
可以看出我们定义了一堆让人眼花的变量,但是没办法,我们必须这样做.
由于字符串长度未知,因此参数 @string 选择了 nvarchar(max).
函数返回值是 int (至少我们认为捕获成功后是这样的).
由于截取字符串的时候,最左端起码从 1 开始截取,所以我们 @index 初始化为 1.
@newLength 表示的我们循环截取字符串时每次要截取的长度.
@newString 表示的是我们每次截得的字符串.
首先我们 SELECT @newString =SUBSTRING(@string,@index,@newLength) 得到截取字符串(第一次是全部).
然后是 SELECT @number = TRY_CONVERT(int,@newString) 尝试转换, try_convert() 不成功会返回 null.
因此我们接着判断是否成功,由于 null 不会大于等于 0 ,只有为数字的时候才会为 true 条件,条件一旦为true 则成功,我们就返回.
但是如果不成功,我们就继续进行往后切取字符串,当然这必须把截取长度 @newLength 和 起始下标位置 @index 对应递增和递减.
一直循环下去,直到成功或者失败,然后返回结果,
我们在使用的时候提示一点的是,我们的函数可能返回 null(当所有尝试失败的时候),因此我们要进行一个子查询过滤:
select * from
(select dbo.MyConvertAndGetNumber(str) as number from TempTable) as t1
where number>0
感兴趣的朋友可以试试哦!很有趣的哦.
我的老师说,世界这么乱,要学东西就学不变的,什么是不变的呢? 答案是: 真理,理论,思路...
在你掌握了这些不变的东西之后,尽管有的时候我们没有这东西,但是我们可以用现有的东西去实现我们要实现的东西,从而 "拼凑" 或 "建造" 一个具有新功能的东西,那么你的自学能力或者说是创新能力就很棒!
同样,举一反N:
现在你能建造用于提取字符串中穿插(如: fd78HJK8HJF9)的数字,然后拼接为新的字符串了吗?
现在你能建造用于提取字符串里边最后为数字且有三位以上位数的数字了吗?
现在你能建造用于提取字符串(fdjskl789)前面的字符的方法吗?
.....
谢谢你看到这里,以下是废话,朋友,你可以 ctrl+w 了!
事就这么个事,不过我相信看到此处的你已经明白了!要感兴趣但不大明白,可以 home 一下继续查看.
要知道,心有多狠,想法就有多狠,写出的代码自然就狠.
好的,朋友,再见!
