SQL Server Collation
Sometimes you need to work with different SQL Server collation depend on your customers. If you need to have multiple languages in same database then you must use Unicode supported data types. If you have different language with different database then, you can have different collation for each database.
- How SQL Server collation works?
- SQL Server Collation with “LIKE” Operator
Following examples will help you to get more idea,
use tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblCollate]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblCollate];
END
GO
CREATE TABLE tblCollate
(varcharGeneral varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
varcharCollateCIAI varchar(100) COLLATE Finnish_Swedish_CI_AI NOT NULL,
varcharCollateCSAI varchar(100) COLLATE Finnish_Swedish_CS_AI NOT NULL,
varcharCollateCSAS varchar(100) COLLATE Finnish_Swedish_CS_AS NOT NULL,
varcharCollateCIAS varchar(100) COLLATE Finnish_Swedish_CI_AS NOT NULL
);
GO
--Insert Record Set 01
INSERT INTO [dbo].[tblCollate]
(varcharGeneral,varcharCollateCIAI,varcharCollateCSAS,varcharCollateCSAI,varcharCollateCIAS)
VALUES ('Varchar','Varchar','Varchar','Varchar','Varchar'),
('varchar','varchar','varchar','varchar','varchar'),
('Value','Value','Value','Value','Value'),
('value','value','value','value','value'),
('Vacation','Vacation','Vacation','Vacation','Vacation'),
('vacation','vacation','vacation','vacation','vacation');
GO
--Insert Record Set 02
INSERT INTO [dbo].[tblCollate]
(varcharGeneral,varcharCollateCIAI,varcharCollateCSAS,varcharCollateCSAI,varcharCollateCIAS)
VALUES ('Wallet','Wallet','Wallet','Wallet','Wallet'),
('wallet','wallet','wallet','wallet','wallet'),
('Wallpaper','Wallpaper','Wallpaper','Wallpaper','Wallpaper'),
('wallpaper','wallpaper','wallpaper','wallpaper','wallpaper'),
('Watch','Watch','Watch','Watch','Watch'),
('watch','watch','watch','watch','watch');
GO
SELECT * FROM [dbo].[tblCollate]; --All Records
--SQL_Latin1_General_CP1_CI_AS
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Va%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%va%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Wa%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Value%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Wallet%');
--Finnish_Swedish_CI_AI
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Va%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%va%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Wa%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Value%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Wallet%');
--Finnish_Swedish_CS_AI
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Va%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%va%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Wa%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Value%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Wallet%');
--Finnish_Swedish_CS_AS
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Va%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%va%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Wa%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Value%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Wallet%');
--Finnish_Swedish_CI_AS
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Va%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%va%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Wa%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Value%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Wallet%');
GO
Now you will have some good idea about SQL Server collation and how you have to change collation with different languages,
Hope following Microsoft Reference will help you for further investigation,
- SQL Server Collation Fundamentals
- LIKE (Transact-SQL)
- Selecting a SQL Server Collation
- COLLATE (Transact-SQL)
Cheers...
and Most welcome your comments and ideas...
No comments:
Post a Comment