SQL 取出字符串中的汉字-字母或是数字.docxVIP

go--创建函数(得到字符串中的汉字)create?function?[dbo].[m_getchinese](????@chinese?nvarchar(max))returns?varchar(100)asbegin????while?patindex(%[^吖-咗]%,@chinese)??0????begin???????set?@chinese?=?stuff(@chinese,patindex(%[^吖-咗]%,@chinese),1,N);????end????return?@chineseendgo--创建函数(得到字符串中的字母)create?function?[dbo].[m_getstr](@maco?varchar(100))returns?varchar(max)asbegin????while?patindex(%[^a-z]%,@maco)??0???????begin???????????set?@maco=stuff(@maco,patindex(%[^a-z]%,@maco),1,)???????end????return?@macoendgo--创建函数(得到字符串中的数字)create?function?[dbo].[m_getnumber](???@mysql_one?nvarchar(200))returns?varchar(200)begin????declare?@mysql_two?varchar(200)????select?@mysql_two=????substring(@mysql_one,patindex(%[0-9.]%,@mysql_one),patindex(%[^0-9.]%,substring(@mysql_one,patindex(%[0-9.]%,@mysql_one),????len(@mysql_one)-patindex(%[0-9.]%,@mysql_one)+1))-1)????return?@mysql_two;end?--测试select?dbo.[m_getchinese](China2009中国HRB4-1v)select?dbo.[m_getstr](China2009中国HRB4-1v)select?dbo.[m_getnumber](China2009中国HRB4-1v)?--运行结果/*-----------中国-----------ChinaHRBv-----------2009*/?--说明一下--上面这个取数字是可以取浮点型的select?dbo.[m_getnumber]?(字段.456A(AA)A)--正常select?dbo.[m_getnumber]?(CHinese2.1day)--正常select?dbo.[m_getnumber]?(Name5.01From)--正常select?dbo.[m_getnumber]?(9898Address)--正常select?dbo.[m_getnumber]?(aaaaaForm2.3333)--错误?--修正函数go/*?取出字符串中间的数字(第二版)*/create?function?[dbo].[m_getnumberV2.0](???????@mysql_one?nvarchar(200))returns?varchar(200)begin????declare?@mysql_two?varchar(200)????declare?@sql_one?int????declare?@sql_two?int????select?@sql_one=?patindex(%[0-9.]%,@mysql_one)????select?@sql_two=????patindex(%[^0-9.]%,????substring(@mysql_one,patindex(%[0-9.]%,@mysql_one),len(@mysql_one)-patindex(%[0-9.]%,@mysql_one)+1))????if?@sql_two=0???????begin???????????select?@mysql_two=?substring?(@mysql_one,@sql_one,len(@mysql_one)+1-@sql_one)???????end????else???????begin???????????select?@mysql_two=substring?(@mysql_one,@sql_one,@sql_two-1)???????end????return?@mysql_two;end?--测试示例select?dbo.[m_getnumberV2.0]?(字段