数据库使用技巧――SQL 全角与半角切换_MySQL
bitsCN.com
数据库系统中,经常有些用户在输入数据的时候会不小心使用全角输入,这就有可能会导致我们的程序出错,如何解决此类问题了。
测试代码:
select cast('111' as int) as num1
select cast('111' as int) as num2
运行结果:
第一个正确显示: 111
第二个则报错: 在将 varchar 值 '111' 转换成数据类型 int 时失败。
下面使用自定义标量函数来解决这个问题:
if object_id(N'u_convert',N'FN') is not null
drop function u_convert
GO
转换原理
全角字符unicode编码从65281~65374
半角字符unicode编码从33~126
空格比较特殊,全角为 12288,半角为 32
而且除空格外,全角/半角按unicode编码排序在顺序上是对应的
所以可以直接通过用+-法来处理非空格数据,对空格单独处理
like的时候,指定排序规则 COLLATE Latin1_General_BIN
是保证字符顺序按unicode编码排序
*/
create function u_convert(
@str nvarchar(4000), --要转换的字符串
@flag bit --转换标志,0转换成半角,1转换成全角
)
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
测试语句:
select cast('111' as int) as num1
select cast('111' as int) as num2
运行结果:
第一个正确显示: 111
第二个则报错: 在将 varchar 值 '111' 转换成数据类型 int 时失败。
下面使用自定义标量函数来解决这个问题:
if object_id(N'u_convert',N'FN') is not null
drop function u_convert
GO
转换原理
全角字符unicode编码从65281~65374
半角字符unicode编码从33~126
空格比较特殊,全角为 12288,半角为 32
而且除空格外,全角/半角按unicode编码排序在顺序上是对应的
所以可以直接通过用+-法来处理非空格数据,对空格单独处理
like的时候,指定排序规则 COLLATE Latin1_General_BIN
是保证字符顺序按unicode编码排序
*/
create function u_convert(
@str nvarchar(4000), --要转换的字符串
@flag bit --转换标志,0转换成半角,1转换成全角
)
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
测试语句:

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



With the rapid development of social media, Xiaohongshu has become one of the most popular social platforms. Users can create a Xiaohongshu account to show their personal identity and communicate and interact with other users. If you need to find a user’s Xiaohongshu number, you can follow these simple steps. 1. How to use Xiaohongshu account to find users? 1. Open the Xiaohongshu APP, click the "Discover" button in the lower right corner, and then select the "Notes" option. 2. In the note list, find the note posted by the user you want to find. Click to enter the note details page. 3. On the note details page, click the "Follow" button below the user's avatar to enter the user's personal homepage. 4. In the upper right corner of the user's personal homepage, click the three-dot button and select "Personal Information"

In Ubuntu systems, the root user is usually disabled. To activate the root user, you can use the passwd command to set a password and then use the su- command to log in as root. The root user is a user with unrestricted system administrative rights. He has permissions to access and modify files, user management, software installation and removal, and system configuration changes. There are obvious differences between the root user and ordinary users. The root user has the highest authority and broader control rights in the system. The root user can execute important system commands and edit system files, which ordinary users cannot do. In this guide, I'll explore the Ubuntu root user, how to log in as root, and how it differs from a normal user. Notice

Win11 Tips Sharing: One trick to skip Microsoft account login Windows 11 is the latest operating system launched by Microsoft, with a new design style and many practical functions. However, for some users, having to log in to their Microsoft account every time they boot up the system can be a bit annoying. If you are one of them, you might as well try the following tips, which will allow you to skip logging in with a Microsoft account and enter the desktop interface directly. First, we need to create a local account in the system to log in instead of a Microsoft account. The advantage of doing this is

Analysis of user password storage mechanism in Linux system In Linux system, the storage of user password is one of the very important security mechanisms. This article will analyze the storage mechanism of user passwords in Linux systems, including the encrypted storage of passwords, the password verification process, and how to securely manage user passwords. At the same time, specific code examples will be used to demonstrate the actual operation process of password storage. 1. Encrypted storage of passwords In Linux systems, user passwords are not stored in the system in plain text, but are encrypted and stored. L

In C language, it represents a pointer, which stores the address of other variables; & represents the address operator, which returns the memory address of a variable. Tips for using pointers include defining pointers, dereferencing pointers, and ensuring that pointers point to valid addresses; tips for using address operators & include obtaining variable addresses, and returning the address of the first element of the array when obtaining the address of an array element. A practical example demonstrating the use of pointer and address operators to reverse a string.

We often create and edit tables in excel, but as a novice who has just come into contact with the software, how to use excel to create tables is not as easy as it is for us. Below, we will conduct some drills on some steps of table creation that novices, that is, beginners, need to master. We hope it will be helpful to those in need. A sample form for beginners is shown below: Let’s see how to complete it! 1. There are two methods to create a new excel document. You can right-click the mouse on a blank location on the [Desktop] - [New] - [xls] file. You can also [Start]-[All Programs]-[Microsoft Office]-[Microsoft Excel 20**] 2. Double-click our new ex

VSCode (Visual Studio Code) is an open source code editor developed by Microsoft. It has powerful functions and rich plug-in support, making it one of the preferred tools for developers. This article will provide an introductory guide for beginners to help them quickly master the skills of using VSCode. In this article, we will introduce how to install VSCode, basic editing operations, shortcut keys, plug-in installation, etc., and provide readers with specific code examples. 1. Install VSCode first, we need

Win11 tricks revealed: How to bypass Microsoft account login Recently, Microsoft launched a new operating system Windows11, which has attracted widespread attention. Compared with previous versions, Windows 11 has made many new adjustments in terms of interface design and functional improvements, but it has also caused some controversy. The most eye-catching point is that it forces users to log in to the system with a Microsoft account. For some users, they may be more accustomed to logging in with a local account and are unwilling to bind their personal information to a Microsoft account.
