Several methods of case sensitivity in SQL Server

Source: Internet
Author: User
Tags integer lowercase query rtrim
server|sqlserver| First type: (cricket)
ALTER TABLE TB
ALTER COLUMN colname nvarchar (MB) COLLATE chinese_prc_ci_as
--No case sensitive
ALTER TABLE TB
ALTER COLUMN colname nvarchar (MB) COLLATE chinese_prc_cs_as
--Case sensitive
ALTER DATABASE COLLATE Chinese_prc_cs_as

The second type: (tree)
--Create the following user-defined functions (UDF)

CREATE FUNCTION StrComp (@Str1 VARCHAR), @Str2 VARCHAR (50))
--alter FUNCTION StrComp (@Str1 VARCHAR), @Str2 VARCHAR (50))
RETURNS INTEGER
As
BEGIN
DECLARE @i INTEGER
--declare @Str1 VARCHAR (50)
--declare @Str2 VARCHAR (50)
DECLARE @y INT
--set @Str1 = ' a '
--set @Str2 = ' A '
SET @i=0
--select ASCII (SUBSTRING (@Str1, @i+1,1))
SET @y=1
DECLARE @iLen INT
SET @iLen = LEN (LTRIM (RTRIM (@Str1))
IF LEN (LTRIM (RTRIM @Str1)) < Len (LTRIM (RTRIM (@Str2))--then
SET @iLen = LEN (LTRIM (RTRIM (@Str2))
while (@i < @iLen)
BEGIN
IF (ASCII (SUBSTRING (@Str1, @i+1,1)) =ascii (SUBSTRING (@Str2, @i+1,1))--then
SET @i = @i +1
ELSE
BEGIN
SET @y=0
Break
End
End
Return @y
End

Test:
SELECT *
From Table1
Where dbo. StrComp (Field1, ' AAbB ') =1

The third type: (Oliver)
Text information in a SQL Server database can be stored in a combination of uppercase, lowercase letters, or both. For example, the last name can appear in the form of "Smith", "Smith" or "Smith".

Whether a database is case-sensitive depends on how SQL Server is installed. If the database is case-sensitive, when searching for text data, you must construct the search condition with the correct combination of uppercase and lowercase letters. For example, if you search for the name "Smith", you cannot use the search term "=smith" or "=smith".

Also, if the server is installed to be case-sensitive, the name of the database, owner, table, and column must be provided with the correct combination of uppercase and lowercase letters. If the supplied name case does not match, SQL Server returns an error and reports "Invalid object name."

When you create a query by using the Diagram pane and Grid pane, the Query designer always correctly reflects whether the server is case-sensitive. However, if you enter a query in the SQL pane, you must be careful to match the name to the way the server interprets the name.

If the server is installed with a case-insensitive option, the
Tip To determine whether the server is case-sensitive, execute the stored procedure sp_server_info, and then check the contents of line 18th. If the server is installed with a case-insensitive setting, the Sort_order option is set to case-insensitive. You can run stored procedures from Query Analyzer.

Type Fourth: (Non-Cloud)
SELECT * from servers where convert (varbinary, name) =convert (varbinary, N ' Rockey ')

The fifth kind: ()

ASCII (' a ') with substring ()



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.