When we use MSSQL, sometimes there are full-width numbers in the table, but our system cannot use them correctly. In this case, we need to replace them with half-width numbers. SQL does not seem to have this function.
No. Do it yourself:
The following is a function I wrote. Of course, my data volume is small and I didn't consider performance!
-- Convert the full-width data to a half-width number (note that only the full-width data can be converted and returned when the first half-width data is returned)
-- Company: Shijiazhuang chuanglian Technology
-- Compilation: Niu kunliang QQ: 273352165
Create Function Ufn_convertwidenumerictoansi
(
@ Vstrin Varchar ( 1000 )
)
Returns Varchar ( 4000 )
As
Begin
Declare @ Strreturn Varchar ( 4000 )
, @ Bin Varbinary ( 4000 )
, @ Str Varchar ( 4000 )
, @ Stmp Varchar ( 4 )
, @ I Int
, @ Len Int
-- , @ Vstrin varchar (1000)
-- Set @ vstrin = '20140901'
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 @ Stmp = Replace ( @ Stmp , ' B ' , '' )
-- Print @ stmp
Set @ Stmp = Cast (( Convert ( Int , @ Stmp ) - 30 ) As Varchar ( 1 ))
Set @ Strreturn = @ Strreturn + @ Stmp
Set @ I = @ I + 4
End
-- Print @ strreturn
Return @ Strreturn
End
-- Example
Select ' 031851001845 ' As Org, DBO. ufn_convertwidenumerictoansi ( ' 031851001845 ' ) Des
Org des
-- -------------------------------------------------
031851001845 031851001845