Replace full-width digits in SQL with a custom function as half-width digits

Function
When we use MSSQL, sometimes the table has a number of full-width digits, but our system can not use them correctly, then we have to replace them with half-width digits. And SQL itself doesn't seem to have this function.

No, we do it ourselves:

The following is a function I wrote myself, of course, my data volume is small, do not consider the performance!

--Convert full-width data into half-width digits (note that this can only be converted to full angle, in the first half of the data return)--Company: Shijiazhuang Chuang Lian Technology--writing: New Kunliang qq:273352165

Create function Ufn_convertwidenumerictoansi (@vstrIn varchar (1000)) returns varchar (4000) Asbegin declare @strReturn varchar (4000), @bin varbinary (4000), @str varchar (4000), @stmp varchar (4), @i int, @len int--, @vstrIn varchar ( 1000)

--set @vstrIn = ' 031851001845 ' Set @strReturn = ' Set @bin =convert (varbinary (4000), @vstrIn) exec master. Xp_varbintohexstr @bin, @str out select @str =stuff (@str, 1,2, ' ") Set @len =len (@str) set @i=1 while @i< @len begin SET @ STMP = substring (@str, @i,4) if (substring (@stmp, 1,1) <> ' a ') return @vstrIn set @stmp = replace (@stmp, ' a ', ') set @s TMP = replace (@stmp, ' B ', ')--print @stmp Set @stmp = Cast ((CONVERT (int, @stmp) -30) as varchar (1)) Set @strReturn = @strRetu RN + @stmp set @i=@i+4 end--print @strReturnreturn @strReturnend

Example

Select ' 031851001845 ' as ORG, Dbo.ufn_convertwidenumerictoansi (' 031851001845 ') DES

ORG DES---------------------------------------------------031851001845 031851 001845