Replace the full-angle number in SQL with a custom function with a half-angle number.

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.