Database usage tips-SQL full-angle and half-angle switchover _ MySQL

Database usage tips-SQL full-angle and half-angle switch bitsCN.com

In the database system, some users may accidentally use full-width input when entering data, which may lead to program errors and how to solve these problems.

Test code:

Select cast ('200' as int) as num1

Select cast ('200' as int) as num2

Running result:

The first correct display: 111

The second error is returned: An error occurred while converting the varchar value '20160301' to the data type int.

The following uses a UDF to solve this problem:

If object_id (n'u _ convert', n'fn ') is not null
Drop function u_convert
GO

Conversion principle

Full-width unicode encoding from 65281 ~ 65374

The unicode encoding of halfwidth characters ranges from 33 ~ 126

The space is special. the width is 12288 and the width is 32.

Besides spaces, the full/half-width values are sorted in unicode encoding order.

Therefore, you can use the +-method to process non-space data and separate the space.

When like, specify the sorting rule COLLATE Latin1_General_BIN

Is to ensure that the character order is sorted by unicode encoding

*/
Create function u_convert (
@ Str nvarchar (4000), -- string to be converted
@ Flag bit -- conversion flag. 0 is converted to halfwidth, and 1 is converted to fullwidth.
)
Returns nvarchar (4000)
AS
Begin
Declare
@ Pat nvarchar (8 ),
@ Step int,
@ I int,
@ Spc int
If @ flag = 0
Begin
Select @ pat = n' % [! -~] % ', @ Step =-65248,
@ Str = replace (@ str, N'', N '')
End
Else
Begin
Select @ pat = n' % [! -~] % ', @ Step = 65248,
@ Str = replace (@ str, N'', N '')
End
Set @ I = patindex (@ pat collate LATIN1_GENERAL_BIN, @ str)
While @ I> 0
Select @ str = replace (@ str,
Substring (
@ Str, @ I, 1 ),
Nchar (unicode (substring (@ str, @ I, 1) + @ step )),
@ I = patindex (@ pat collate LATIN1_GENERAL_BIN, @ str)
Return (@ str)
End
GO

Test statement:

Select cast ('200' as int) as num1

Select cast ('200' as int) as num2

Running result:

The first correct display: 111

The second error is returned: An error occurred while converting the varchar value '20160301' to the data type int.

The following uses a UDF to solve this problem:

If object_id (n'u _ convert', n'fn ') is not null
Drop function u_convert
GO

Conversion principle

Full-width unicode encoding from 65281 ~ 65374

The unicode encoding of halfwidth characters ranges from 33 ~ 126

The space is special. the width is 12288 and the width is 32.

Besides spaces, the full/half-width values are sorted in unicode encoding order.

Therefore, you can use the +-method to process non-space data and separate the space.

When like, specify the sorting rule COLLATE Latin1_General_BIN

Is to ensure that the character order is sorted by unicode encoding

*/
Create function u_convert (
@ Str nvarchar (4000), -- string to be converted
@ Flag bit -- conversion flag. 0 is converted to halfwidth, and 1 is converted to fullwidth.
)
Returns nvarchar (4000)
AS
Begin
Declare
@ Pat nvarchar (8 ),
@ Step int,
@ I int,
@ Spc int
If @ flag = 0
Begin
Select @ pat = n' % [! -~] % ', @ Step =-65248,
@ Str = replace (@ str, N'', N '')
End
Else
Begin
Select @ pat = n' % [! -~] % ', @ Step = 65248,
@ Str = replace (@ str, N'', N '')
End
Set @ I = patindex (@ pat collate LATIN1_GENERAL_BIN, @ str)
While @ I> 0
Select @ str = replace (@ str,
Substring (
@ Str, @ I, 1 ),
Nchar (unicode (substring (@ str, @ I, 1) + @ step )),
@ I = patindex (@ pat collate LATIN1_GENERAL_BIN, @ str)
Return (@ str)
End
GO

Test statement:

BitsCN.com