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)

164 questions
81
votes
13 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,922
  • 6
  • 43
  • 70
66
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,683
  • 5
  • 24
  • 30
38
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
  • 487
  • 2
  • 5
  • 7
36
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
  • 12,368
  • 8
  • 45
  • 80
25
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
  • 2,063
  • 8
  • 39
  • 65
19
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
  • 30,824
  • 50
  • 153
  • 232
18
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
  • 9,148
  • 20
  • 67
  • 99
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,746
  • 3
  • 28
  • 37
12
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
  • 8,362
  • 6
  • 61
  • 88
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
  • 664
  • 3
  • 14
  • 32
8
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
  • 42,737
  • 46
  • 157
  • 243
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
  • 875
  • 2
  • 14
  • 22
7
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,344
  • 15
  • 23
6
votes
4 answers

SQL Server turkish character situation

I am having a Turkish Character problem in ASP.NET and SQL Server. I have searchbox in asp.net and trying to do a search in database. However, I am having problems in Turkish characters. When I search for "GALVANİZ" which contains illegal "İ"…
Arif YILMAZ
  • 5,754
  • 26
  • 104
  • 189
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
1
2 3
…
10 11