store data

Question

store data

sqlfriend

SSC Guru

Points: 52781
More actions
February 8, 2022 at 8:33 pm

#3981384
We are setting up a new database. One ask is for students who want to display a non-Latin name are able to with latin chars in (). or Spanish accent like ó?
What options do we have, for example, use a special collation for db, or table, or column? If so, what collation to use?
or other options?
Thanks
- This topic was modified 2 years, 11 months ago by sqlfriend.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1385 More actions · Answer 1

Not an expert, but it looks like you might want to use Modern_Spanish_CI_AS collation (case insensitive, but accent sensitive -- so a = A but à <> a).

e.g., for a database named "Mod_ES_CI_AI", the following appears to work:

ALTER DATABASE [Mod_ES_CI_AI] COLLATE Modern_Spanish_CI_AS;
GO

CREATE TABLE [dbo].[SpanishText](
[SpanishTextID] [int] IDENTITY(1,1) NOT NULL,
[SpanishText] [varchar](255) NOT NULL,
 CONSTRAINT [PK_SpanishText] PRIMARY KEY CLUSTERED 
(
[SpanishTextID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO dbo.SpanishText (SpanishText)
VALUES ('A él le gusta el queso'),
('Te recomiendo que pruebes el té'),
('Sí, quiero ir al restaurante, pero solo si tienen pizza'),
('Quiero más chocolate, mas es mala idea.'),
('¿Donde esta el baño?'),
('ambigüedad');

INSERT INTO dbo.SpanishText (SpanishText)
VALUES ('A él le gusta el queso'),
       ('Te recomiendo que pruebes el té'),
       ('Sí, quiero ir al restaurante, pero solo si tienen pizza'),
       ('Quiero más chocolate, mas es mala idea.'),
       ('¿Donde esta el baño?'),
       ('ambigüedad');
SELECT * FROM dbo.SpanishText;
SELECT * FROM dbo.SpanishText WHERE SpanishText LIKE '%ü%';
SELECT * FROM dbo.SpanishText WHERE SpanishText NOT LIKE '%ü%';
SELECT * FROM dbo.SpanishText WHERE SpanishText LIKE '%u%';
SELECT * FROM dbo.SpanishText WHERE SpanishText NOT LIKE '%u%';

SELECT * FROM dbo.SpanishText WHERE SpanishText LIKE '%é%';
SELECT * FROM dbo.SpanishText WHERE SpanishText NOT LIKE '%é%';
SELECT * FROM dbo.SpanishText WHERE SpanishText LIKE '%e%';
SELECT * FROM dbo.SpanishText WHERE SpanishText NOT LIKE '%e%';

TRUNCATE TABLE dbo.SpanishText;

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thank you. It looks like the Modern_Spanish_CI_AS stored spanish mainly. What we want is still a database mostly stored English, but just for sometimes if users like to display their non-latin name in a () for example.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

sqlfriend wrote:

Thank you. It looks like the Modern_Spanish_CI_AS stored spanish mainly. What we want is still a database mostly stored English, but just for sometimes if users like to display their non-latin name in a () for example.

You don't have to make the whole database non-english nor do you have to make it case sensitive for just a few name columns. In most cases, NVARCHAR() will do the trick for you and if you want to make that column case sensitive, you could do that with with a binary collation or other collation just on the column(s) needed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 4

NVARCHAR for the win!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning