Stack Overflow
Stack Overflow

Questions tagged [collate]

Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

COLLATE (Transact-SQL)

142 questions
65
votes
3 answers

SQLite Query in non case sensitive alphabetical order

All I want to do is grab the stuff in alphabetical order and ignore the capital letters. db.rawQuery("SELECT " + catName + " FROM "+tableName+" ORDER BY "+catName+" ASC COLLATE NOCASE;", null); This is the code I'm using above, but it always gives…
Anthony Honciano
  • 1,513
  • 5
  • 23
  • 30
65
votes
10 answers

Change collations of all columns of all tables in SQL Server

I imported a database with some data to compare with another database. The target database has collation Latin1_General_CI_AS and the source database has SQL_Latin1_General_CP1_CI_AS. I did change the collation of the source database in general to…
YvesR
  • 5,173
  • 6
  • 38
  • 65
33
votes
3 answers

How do I perform a case-sensitive search using LIKE?

I'm trying to find records that contain a string of 6 or more alpha-numeric characters in uppercase. Some examples: PENDING 3RDPARTY CODE27 I'm using the following statement: SELECT Details FROM MyTable WHERE Details LIKE…
Hand-E-Food
  • 11,154
  • 8
  • 40
  • 75
24
votes
2 answers

How to use the COLLATE in a JOIN in SQL Server?

I´m trying to join two tables but I get this error: Msg 468, Level 16, State 9, Line 8 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. This is the code I´m…
D.Roca
  • 347
  • 2
  • 4
  • 7
20
votes
3 answers

Use Collate in CONCAT

I was trying to concatonate 2 columns with a whitespace in between and got a collation error: SELECT DISTINCT p.PERSON_ID, p.ID_NUMBER, CONCAT(p.FULLNAMES, CONCAT(' ', p.SURNAME)) AS NAME, o.ORG_NAME, w.WARD_DESCRIPTION AS WARD, …
Carel
  • 1,849
  • 8
  • 28
  • 58
18
votes
5 answers

normalizing accented characters in MySQL queries

I'd like to be able to do queries that normalize accented characters, so that for example: é, è, and ê are all treated as 'e', in queries using '=' and 'like'. I have a row with username field set to 'rené', and I'd like to be able to match on it…
George Armhold
  • 29,784
  • 45
  • 147
  • 224
13
votes
2 answers

What does the COLLATE keyword do when creating a sqlite index?

According to the sqlite3 documentation, The COLLATE clause following each column name defines a collating sequence used for text entries in that column. The default collating sequence is the collating sequence defined for that column in…
Andrey Fedorov
  • 7,836
  • 20
  • 63
  • 95
13
votes
5 answers

Using COLLATE in Android SQLite - Locales is ignored in LIKE statement

When creating my SQLite database in Android I set the database locale - db.setLocale(new Locale("cz_CZ")). This is a Czech locale. A SELECT statement works and takes the locale into account, for example: SELECT * from table WHERE name='sctzy'…
Daniel Novak
  • 2,590
  • 3
  • 25
  • 36
11
votes
2 answers

Correct way to set collation in temporary table column TSQL

I have a temporary table which gets data inserted using bulk insert. However, when I want to update data from temp table to a normal table it gives collation problems. I know how to solve this by using something like: UPDATE RegularTable SET…
J3FFK
  • 584
  • 2
  • 11
  • 28
9
votes
1 answer

Collate declared SQL variable

I've been looking at this code, reproduced below, that looks for non-ASCII characters... select line, patindex('%[^ !-~]%' COLLATE Latin1_General_BIN, Line) as [Position], substring(Line, patindex('%[^ !-~]%' COLLATE Latin1_General_BIN, Line),…
Michael
  • 5,910
  • 4
  • 52
  • 74
7
votes
2 answers

PostgreSQL 9.1 using collate in select statements

I have a postgresql 9.1 database table, "en_US.UTF-8": CREATE TABLE branch_language ( id serial NOT NULL, name_language character varying(128) NOT NULL, branch_id integer NOT NULL, language_id integer NOT NULL, .... ) The…
Henri
  • 795
  • 1
  • 13
  • 22
7
votes
2 answers

T-SQL Table variable with case sensitive columns - collate SQL_Latin1_General_CP1_CS_AS

Is it possible to have collate SQL_Latin1_General_CP1_CS_AS in table variable columns' definitions? The reason I want to do this is because I have case sensitive information in my source table but when I insert it in the table variable there is a…
gotqn
  • 36,464
  • 39
  • 145
  • 218
6
votes
2 answers

ALTER DATABASE to change COLLATE not working

I am using Django on Bluehost. I created a form for user generated input, but unicode inputs from this form fails to be stored or displayed of characters. So I did a SO and google search that I should change the Collate and Character set of my…
TonyTony
  • 1,156
  • 11
  • 22
5
votes
1 answer

Syntax for using collate nocase in a SQLite replace function

I have an existing database where they created theiw own unicode collation sequence. I'm trying to use the following code and get a "no such collation sequence" exception. Can anybdy hlep with the the syntax to use "collate nocase" with this…
Jim
  • 51
  • 2
4
votes
1 answer

Unicode-ready wordsearch - Question

Is this code OK? I don't really have a clue which normalization-form I should us (the only thing I noticed is with NFD I get a wrong output). #!/usr/local/bin/perl use warnings; use 5.014; use utf8; binmode STDOUT, ':encoding(utf-8)'; use…
sid_com
  • 21,289
  • 23
  • 89
  • 171
1
2 3
…
9 10