sql语句中怎么去掉数字后面的字符

pengpeng409 2013-11-09 07:46:16
sql语句怎么去掉drug_spec字段中数字后面的字符如mg，g，ml，万u，ug，然后把该字段转化为float型的，除了用replace还有没有其他的方法
示例如下：
durg_spec
0.25ug
0.25ug
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
2.5mg
2.5mg
2.5mg
2.5mg
0.15g
0.15g
0.3g
0.3g
0.2mg
0.2mg
0.2mg
0.2mg
0.2mg
0.2mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
8.8mg
8.8mg
8.8mg
8.8mg
8.8mg
8.8mg
8.8mg
8.8mg
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
1mg
1mg
1mg
1mg
1mg
1mg
1mg
1mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
30mg
20mg
20mg
20mg
20mg
20mg
20mg
20mg
20mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
20mg
20mg
20mg
20mg
20mg
20mg
20mg
20mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g






40mg
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.25g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
2mg
10mg
10mg
10mg
10mg
10mg
10mg
15mg
15mg
15mg
15mg
15mg
15mg
15mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
10mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
25mg
25mg
25mg
10mg
10mg
10mg
0.5mg
0.5mg
0.5mg
0.5mg
0.5mg
0.5mg
0.5mg
0.5mg
0.5mg



5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
25mg
25mg
10mg
30mg
30mg
0.15g
0.15g
50mg
50mg
50mg
50mg
0.3g
0.3g
0.3g
0.3g
25mg
25mg
25mg
25mg


0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.2g
0.25mg
0.25mg
0.25mg
0.25mg
0.25mg
0.25mg
0.25mg
0.25mg
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.1g
0.5g
0.5g
0.5g
0.5g
0.5g
0.5g
40mg
40mg
40mg
40mg
40mg
40mg
40mg
40mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
5mg
25mg
25mg
25mg
25mg
25mg
25mg
25mg
25mg
20mg
20mg
20mg
20mg
20mg
20mg
20mg
20mg
0.33g
0.33g
0.33g
0.2g
0.2g
0.25mg
0.25mg
0.25mg
0.25mg
75mg
75mg
75mg
75mg
75mg
75mg
75mg
75mg
0.5g
...全文
742 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
引用 15 楼 pengpeng409 的回复:
可以了，谢谢，你太厉害了。。。
哦 ，呵呵，那就好啊
pengpeng409 2013-11-09
  • 打赏
  • 举报
 回复
可以了，谢谢，你太厉害了。。。
Andy__Huang 2013-11-09
  • 打赏
  • 举报
 回复
引用 10 楼 pengpeng409 的回复:
100's 100' 100's 100' 100's 100' 100's 100' 100's 100' 100's 100' 100's 100'
这个经过转换后,你想要得到什么结果? 100 还是 100100 ??
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
引用 11 楼 pengpeng409 的回复:
100'后面的单引号没有去掉
对了还有什么特殊的情况不？ 上面的代码试试看，我已经修改了，考虑了 单引号的情况
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
引用 11 楼 pengpeng409 的回复:
100'后面的单引号没有去掉
再试试： 
select case when isnull(durg_spec,'') =''
                 then null
            else cast(substring(durg_spec,1,patindex('%[''a-z]%',durg_spec)-1) as float)
       end as durg_spec
from tb
pengpeng409 2013-11-09
  • 打赏
  • 举报
 回复
100'后面的单引号没有去掉
pengpeng409 2013-11-09
  • 打赏
  • 举报
 回复
100's 100' 100's 100' 100's 100' 100's 100' 100's 100' 100's 100' 100's 100'
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
引用 7 楼 pengpeng409 的回复:
用这段代码报错： select case when drug_spec is null then drug_spec else cast(substring(drug_spec,1,patindex('%[a-z]%',drug_spec)-1) as float) end durg_spec from atf_ypxx 消息 537，级别 16，状态 2，第 2 行 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
再试试这个： 
select case when isnull(durg_spec,'') =''
                 then null
            else cast(substring(durg_spec,1,patindex('%[a-z]%',durg_spec)-1) as float)
       end as durg_spec
from tb
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
引用 7 楼 pengpeng409 的回复:
用这段代码报错： select case when drug_spec is null then drug_spec else cast(substring(drug_spec,1,patindex('%[a-z]%',drug_spec)-1) as float) end durg_spec from atf_ypxx 消息 537，级别 16，状态 2，第 2 行 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
哦 你的数据里有null值是吧
pengpeng409 2013-11-09
  • 打赏
  • 举报
 回复
用这段代码报错： select case when drug_spec is null then drug_spec else cast(substring(drug_spec,1,patindex('%[a-z]%',drug_spec)-1) as float) end durg_spec from atf_ypxx 消息 537，级别 16，状态 2，第 2 行 传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
方法3： 


;with tb(durg_spec)
as
(
select '0.25ug' union all
select '0.25ug' union all 
select '0.5g' union all
select '0.5g' union all
select '0.5g' union all
select '0.5g' union all
select '0.5g' union all                
select '0.5g' union all                
select '0.5g' union all                
select '0.5g' union all
select '0.5g' union all
select '2.5mg' union all               
select '2.5mg' union all               
select '2.5mg' union all               
select '2.5mg' union all               
select '0.15g' union all               
select '0.15g' union all               
select '0.3g' union all
select '0.3g' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '5mg' 
)

--方法3
select cast(stuff(durg_spec,
                  patindex('%[a-z]%',durg_spec),
                  len(durg_spec)-patindex('%[a-z]%',durg_spec)+1,
                  '') as float) as durg_spec
from tb
/*
durg_spec
0.25
0.25
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
2.5
2.5
2.5
2.5
0.15
0.15
0.3
0.3
0.2
0.2
0.2
0.2
0.2
0.2
5
*/
Andy__Huang 2013-11-09
  • 打赏
  • 举报
 回复 


create table #tb(durg_spec nvarchar(50))
insert into #tb
select '0.25ug'
union all select '0.25ug'
union all select '0.5g'
union all select '0.5g'
union all select '0.5g'
union all select '0.5g'
union all select '0.5g'
union all select '0.5g'
union all select '0.5g'
union all select '0.5g'
union all select '0.5g'
union all select '2.5mg'
union all select '2.5mg'
union all select '2.5mg'
union all select '2.5mg'
union all select '0.15g'
union all select '0.15g'
union all select '0.3g'
union all select '0.3g'
union all select '0.2mg'
union all select '0.2mg'
union all select '0.2mg'
union all select '0.2mg'
union all select '0.2mg'
union all select '0.2mg'
union all select '5mg'
union all select '5mg'
union all select '5mg'
union all select '5mg'
union all select '5mg'
union all select '5mg'
union all select '5mg'
union all select '5mg'
union all select '8.8mg'
union all select '8.8mg'
union all select '8.8mg'
union all select '8.8mg'
union all select '8.8mg'
union all select '8.8mg'
union all select '8.8mg'
union all select '8.8mg'
union all select '0.1g'
union all select '0.1g'
union all select '0.1g'

create Function [dbo].[fn_Number] (@texto varchar(100))
returns varchar(100)
as
begin
declare @s varchar(100),@i int
declare @v varchar(1)
set @i=1
set @s=''
while @i<=len(@texto)
begin
	set @v=substring(@texto,@i,1)
	set @s=@s+case when isnumeric(@v)=1 then @v else '' end
	set @i=@i+1
end
return @s
End 


select [dbo].[fn_Number](durg_spec) from #tb


--结果
/*
0.25
0.25
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
2.5
2.5
2.5
2.5
0.15
0.15
0.3
0.3
0.2
0.2
0.2
0.2
0.2
0.2
5
5
5
5
5
5
5
5
8.8
8.8
8.8
8.8
8.8
8.8
8.8
8.8
0.1
0.1
0.1
*/
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
上面是方法1。 下面是方法2：
;with tb(durg_spec) as ( select '0.25ug' union all select '0.25ug' union all select '0.5g' union all select '0.5g' union all select '0.5g' union all select '0.5g' union all select '0.5g' union all select '0.5g' union all select '0.5g' union all select '0.5g' union all select '0.5g' union all select '2.5mg' union all select '2.5mg' union all select '2.5mg' union all select '2.5mg' union all select '0.15g' union all select '0.15g' union all select '0.3g' union all select '0.3g' union all select '0.2mg' union all select '0.2mg' union all select '0.2mg' union all select '0.2mg' union all select '0.2mg' union all select '0.2mg' union all select '5mg' ) --方法2 select cast(left(durg_spec,patindex('%[a-z]%',durg_spec)-1) as float) as durg_spec from tb /* durg_spec 0.25 0.25 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 2.5 2.5 2.5 2.5 0.15 0.15 0.3 0.3 0.2 0.2 0.2 0.2 0.2 0.2 5 */
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
引用 1 楼 pengpeng409 的回复:
请大牛帮忙看一下，谢谢
我用了一部分的数据，你看看是这样不： 


;with tb(durg_spec)
as
(
select '0.25ug' union all
select '0.25ug' union all 
select '0.5g' union all
select '0.5g' union all
select '0.5g' union all
select '0.5g' union all
select '0.5g' union all                
select '0.5g' union all                
select '0.5g' union all                
select '0.5g' union all
select '0.5g' union all
select '2.5mg' union all               
select '2.5mg' union all               
select '2.5mg' union all               
select '2.5mg' union all               
select '0.15g' union all               
select '0.15g' union all               
select '0.3g' union all
select '0.3g' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '0.2mg' union all
select '5mg' 
)


select cast(substring(durg_spec,1,patindex('%[a-z]%',durg_spec)-1) as float) as durg_spec
from tb
/*
durg_spec
0.25
0.25
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
0.5
2.5
2.5
2.5
2.5
0.15
0.15
0.3
0.3
0.2
0.2
0.2
0.2
0.2
0.2
5
*/
LongRui888 2013-11-09
  • 打赏
  • 举报
 回复
引用 1 楼 pengpeng409 的回复:
请大牛帮忙看一下，谢谢
呵呵，能给个建表的语句不？
pengpeng409 2013-11-09
  • 打赏
  • 举报
 回复
请大牛帮忙看一下，谢谢
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
sql语句中去除字符串中的回车换行
sql语句中去除字符串中的回车换行 trim(列) 去除字符串两头的空格,但回车换行去不掉， replace(replace(列,chr(13),’’),chr(10),’’) oracle中去除 oracle中去除字符串中的换行符号 replace(replace(str,chr(13)...
SQL中去掉非数字的字符，只保留数字
SQL中去掉非数字的字符，只保留数字
SQL中字符串拼接
在SQL语句中经常需要进行字符串拼接，以sqlserver，oracle，mysql三种数据库为例，因为这三种数据库具有代表性。 sqlserver： select '123'+'456'; oracle： select '123'||'456' from dual; 或 select concat('...
SQL语句怎么去掉字段不想要的字符？
在网上找了好多去除字段中不想要的字符的问题，大部分回答都是写什么函数，但是我感觉很麻烦，也不想去想。所以就研究出了这个方法，简单，实用，高效。话不多说，上SQL语句： update resume_info set work_...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章