Home Database Mysql Tutorial 数据库使用技巧――SQL 全角与半角切换_MySQL

数据库使用技巧――SQL 全角与半角切换_MySQL

Jun 01, 2016 pm 01:51 PM
how Skill Database systems user

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

测试语句:

bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to use Xiaohongshu account to find users? Can I find my mobile phone number? How to use Xiaohongshu account to find users? Can I find my mobile phone number? Mar 22, 2024 am 08:40 AM

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"

Log in to Ubuntu as superuser Log in to Ubuntu as superuser Mar 20, 2024 am 10:55 AM

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: Skip Microsoft Account Login with One Trick Win11 Tips Sharing: Skip Microsoft Account Login with One Trick Mar 27, 2024 pm 02:57 PM

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 Analysis of user password storage mechanism in Linux system Mar 20, 2024 pm 04:27 PM

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

A must-have for veterans: Tips and precautions for * and & in C language A must-have for veterans: Tips and precautions for * and & in C language Apr 04, 2024 am 08:21 AM

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.

What are the tips for novices to create forms? What are the tips for novices to create forms? Mar 21, 2024 am 09:11 AM

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 Getting Started Guide: A must-read for beginners to quickly master usage skills! VSCode Getting Started Guide: A must-read for beginners to quickly master usage skills! Mar 26, 2024 am 08:21 AM

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 Win11 Tricks Revealed: How to Bypass Microsoft Account Login Mar 27, 2024 pm 07:57 PM

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.

See all articles