[SQL2005][TSQL] Problème charset/collate

**luunaz** · 16/07/2009, 14h27

Bonjour,

Je suis en train de réaliser une requête sql pour extraire du texte d'un champ image.
Les base/tables n'ont pas été créé par moi mais par le logiciel que j'utilise.

Mon soucis est que le texte extrait contient des accents, et il ne s'affiche pas correctement (Problème de charset je suppose au vu des caractères affichés, tous les caractères à accent s'affichent en deux caractères "bizarres").

Je fais mes requêtes sous "Analyseur de requêtes SQL" et mes bases sont sous SQL SERVER 2005.

Je met ma requête complète au cas où un élément auquel je n'aurais pas pensé interférerait:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select /*hi0.nrid, hi0.date_deb, hi0.ref, hi0.status, hi0.sujet, 
hi0.societe, hi0.personne, hi0.titulaire, hi0.so0_nrid,  hi0.type, 
so0.code_post, so0.loc, so0.var2, so0.var11, */
dm0.title , convert(varchar(1200), convert(binary(1200), substring(dm0.memo,1,1200)))
collate SQL_Latin1_General_CP850_CI_AS 
from SYSADM.hi0 hi0 
left outer join SYSADM.dm5 dm5 
on dm5.hi0_nrid = hi0.nrid 
left outer join SYSADM.dm0 dm0 
on dm5.dm0_nrid = dm0.nrid  
inner join SYSADM.so0 so0 
on hi0.so0_nrid=so0.nrid  
inner join SYSADM.am0 am0 
on hi0.titulaire = am0.titulaire  
inner join SYSADM.am00 am00 
on am00.nrid = am0.team_nrid 
where hi0.ref in  ('C-Visite') and hi0.titulaire = 'R12' and hi0.date_deb >= '01-01-2008'
order by hi0.date_deb, so0.reg_code, hi0.titulaire

J'ai plusieurs questions sur lesquels je n'ai pas trouvé de réponse encore:

Première question:
La différence entre VARCHAR ET NVARCHAR influe t'elle sur la prise en charge de la collation sur la requête, je m'explique, j'ai lu (lien ci-dessous) que NVARCHAR est une donnée UNICODE, mais je ne comprends pas bien, ca veut dire que l'on ne peut mettre que des caractères UNICODE dans un NVARCHAR ? (ie: des chaines utf8?)
Et les VARCHAR sont indépendant du jeu de caractère de la chaîne?
http://msdn.microsoft.com/fr-fr/libr...9(SQL.90).aspx
http://msdn.microsoft.com/fr-fr/libr...9(SQL.90).aspx

Seconde question:
Est-ce que l'extraction de mon champ image est correcte?
convert(varchar(1200),convert(binary(1200), substring(dm0.memo,1,1200)))
J'ai réalisé plusieurs essai avec cast et convert, cela semble est bon.

http://msdn.microsoft.com/en-us/libr...8(SQL.90).aspx
Ici j'ai vu que la conversion d'un champ image est implicite en binary/varbinary(même timestamp).
Ensuite un binary ou un varbinary est convertible implicitement en varchar ou nvarchar.
Si l'on met directement convert(varchar(1200), substring(dm0.memo,1,1200)) par quel mécanisme passe-t-on pour obtenir le varchar, le binary ou le varbinary, cela a t-il une influence sur le jeu de caractère?

Troisième question:
La vrai question, dans un sens, est-ce que la manière d'écrire le collate est correct?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

collate SQL_Latin1_General_CP850_CI_AS

Le nom de l'interclassement "SQL_Latin1_General_CP850_CI_AS" a été obtenu en regardant les propriétés de la base, étant donné que c'est celui de la base, je ne comprends pas pourquoi en sortie (à l'affichage dans les résultats de ma requête)

Pour information j'ai déjà lu les articles suivant:
http://sqlpro.developpez.com/cours/s...er/collations/
Mais cela ne m'a pas aidé.

Je suis arrivé au bout de mes connaissances en matière de jeu de caractère et je remercie d'avance toute personne pouvant me donner piste de recherche ou solution.

PS: Je reste à disposition pour toutes questions complémentaires.

**elsuket** · 17/07/2009, 10h45

Bonjour,

La différence entre VARCHAR et NVARCHAR est effectivement que dans le premier cas vous stockez les données en ASCII, et dans l'autre en Unicode.
L'avantage de l'ASCII est principalement sa taille de stockage (1 caractère = 1 octet), mais il ne permet pas de stocker les lettres des alphabets arabes ou asiatiques par exemple.
Unicode permet de stocker ces caractères, mais il coûte deux fois plus cher (1 caractère = 2 octets).

La collation vous permet de choisir si la casse, les accents, les ligatures, les caractères accentués, ... auront un impact sur les recherches ou les tris que vous voulez effectuer. Évidemment plus votre collation est lâche (insensibilité à la casse, aux accents), plus le moteur de base de données doit effectuer du travail pour répondre à votre requête.

Votre colonne de type IMAGE stocke-t-elle uniquement du texte ? Si tel est le cas, préférez-lui le type VARCHAR(MAX), et si ce n'est pas le cas, alors préférez le type VARBINARY(MAX).
Le type IMAGE, comme le type TEXT, sont dépréciés dès SQL Server 2005.

Si vous passez votre colonne au type VARCHAR(MAX), alors vous pouvez réaliser votre extraction avec la fonction SUBSTRING() directement.
Si vous devez la laisser en type VARBINARY(MAX), cela me semble correct.

Ici j'ai vu que la conversion d'un champ image est implicite en binary/varbinary(même timestamp).

Elle est effectivement implicite mais elle a un coût. Si vous utilisez le type adéquat aux données que vous stockez, vous obtiendrez forcément de meilleures performances.
En revanche je ne comprends pas pourquoi vous parlez de TIMESTAMP.

Par quel mécanisme passe-t-on pour obtenir le varchar, le binary ou le varbinary

La fonction SUBSTRING(), comme de nombreuses autres fonctions à priori destinées aux données de type caractère, elles fonctionnent avec les données de type binaire.

cela a t-il une influence sur le jeu de caractère?

Pas pour la fonction SUBSTRING().

est-ce que la manière d'écrire le collate est correct?

Oui, mais elle implique un travail supplémentaire au moteur de base de données si la collation de la base de données ou de la colonne memo n'est pas la même.

Finalement, vous devez choisir la collation qui convient le mieux à vos besoins, avant de penser à réaliser toute extraction de données.
Les collations sont applicables au niveau base de données, table et colonne.

@++

**luunaz** · 17/07/2009, 11h36

Merci pour ces réponses qui comble quasiment toutes mes attentes

.

Envoyé par elsuket

Votre colonne de type IMAGE stocke-t-elle uniquement du texte ? Si tel est le cas, préférez-lui le type VARCHAR(MAX), et si ce n'est pas le cas, alors préférez le type VARBINARY(MAX).
Le type IMAGE, comme le type TEXT, sont dépréciés dès SQL Server 2005.

Je ne controle pas les types des champs, les tables sur lesquelles je travaille sont celles créer par un logiciel CRM, ce champ de type IMAGE permet de stocker des documents, de ce que j'ai pu en voir ce semble être seulement des fichiers texte mais je ne peux être totalement sûr, je ne peux pas me permettre de changer le type de ce champ.

Envoyé par elsuket

En revanche je ne comprends pas pourquoi vous parlez de TIMESTAMP.

Simplement que cela m'avait marqué sur le tableau disponible sur la page concernant le CONVERT sur msdn, que l'on pouvait convertir le champ IMAGE en TIMESTAMP et cela m'a étonné, mais ce ne concerne pas vraiment ma demande en effet :p.

Envoyé par elsuket

Oui, mais elle implique un travail supplémentaire au moteur de base de données si la collation de la base de données ou de la colonne memo n'est pas la même.

J'ai cherché comment trouver la collation de la colonne, j'ai supposer que cela devait être disponible dans les tables systèmes mais je n'ai pas réussis à la trouver (Je tiens juste à préciser que je ne suis pas du tout habitué à SQL SERVER 2005, habituellement je ne travaille que avec MYSQL). J'ai juste trouvé la collation de la base.
Sauriez-vous me donner la requête pour recupérer cette information ?

Envoyé par elsuket

Finalement, vous devez choisir la collation qui convient le mieux à vos besoins, avant de penser à réaliser toute extraction de données.
Les collations sont applicables au niveau base de données, table et colonne.

Tout le problème se situe ici, je ne trouve pas la bonne collation pour cette requête, un nombre assez grand a été testé mais impossible d'avoir un rendu de charactère accentué correct.
Même la collation de la base ne semble pas correspondre donc je me retrouve un peu perdu arrivé à ce niveau.
Je dois aussi préciser que dans l'application CRM à un endroit de l'application les caractères accentués sont affichés correctement, mais à un autre endroit (la ou est faite ma requête) il ne le sont pas, donc je pense que c'est la collation qui n'est pas la bonne mais je ne sais pas la trouver :p.

**elsuket** · 17/07/2009, 11h48

Je ne controle pas les types des champs, les tables sur lesquelles je travaille sont celles créer par un logiciel CRM, ce champ de type IMAGE permet de stocker des documents, de ce que j'ai pu en voir ce semble être seulement des fichiers texte mais je ne peux être totalement sûr, je ne peux pas me permettre de changer le type de ce champ.

l'éditeur de votre logiciel de CRM est comme un porc à qui on donne de la confiture ! Vous n'avez pas le choix, malheureusement ...

Simplement que cela m'avait marqué sur le tableau disponible sur la page concernant le CONVERT sur msdn, que l'on pouvait convertir le champ IMAGE en TIMESTAMP et cela m'a étonné, mais ce ne concerne pas vraiment ma demande en effet :p.

Pas de problème

J'ai cherché comment trouver la collation de la colonne, j'ai supposer que cela devait être disponible dans les tables systèmes mais je n'ai pas réussis à la trouver (Je tiens juste à préciser que je ne suis pas du tout habitué à SQL SERVER 2005, habituellement je ne travaille que avec MYSQL). J'ai juste trouvé la collation de la base.
Sauriez-vous me donner la requête pour recupérer cette information ?

Essayez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT T.name AS nomTable,
		C.name AS nomColonne,
		C.collation_name AS nomCollationColonne
FROM sys.tables AS T
JOIN sys.columns AS C
	ON T.object_id = C.object_id
WHERE T.name = 'maTable'
AND C.name = 'maColonne'

donc je pense que c'est la collation qui n'est pas la bonne mais je ne sais pas la trouver :p.

Quelles sont les collations de la colonne et de la base de données ?
On va essayer de trouver ça

@++

**luunaz** · 17/07/2009, 13h55

Envoyé par elsuket

l'éditeur de votre logiciel de CRM est comme un porc à qui on donne de la confiture ! Vous n'avez pas le choix, malheureusement ...

Hmm Lol

, Cela me fait beaucoup rire, j'ai personnellement quelques griefs contre ce logiciel mais seulement sur la toute petite partie du logiciel sur laquelle j'ai travaillé, mais je suis pas a même de juger de la qualité général de ce logiciel.

Avec adaptation à mon contexte ma requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select T.name AS nomTable, C.name AS nomColonne, C.collation AS nomCollationColonne
FROM dbo.syscolumns AS C
JOIN dbo.sysobjects AS T
ON T.id = C.id
WHERE c.name = 'memo'
AND T.name = 'dm0'

Résultat:

nomTable | nomColonne | nomCollationColonne
dm0 | memo | NULL

Juste avant que vous me donniez cette requête j'ai lu dans les règles du forum comment générer la requête de création de la table et cela m'avait donné ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
if exists (select * from dbo.sysobjects where id = object_id(N'[SYSADM].[dm0]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [SYSADM].[dm0]
GO
 
CREATE TABLE [SYSADM].[dm0] (
	[nrid] [decimal](15, 0) NOT NULL ,
	[template] [int] NULL ,
	[rid] [varchar] (32) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
	[rmod] [varchar] (32) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[dmod] [datetime] NULL ,
	[code_dep] [int] NULL ,
	[niv] [int] NULL ,
	[author] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[titulaire] [nvarchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[extension] [varchar] (5) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[file_size] [int] NULL ,
	[reference] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
	[version] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[is_visible] [int] NULL ,
	[ntype] [int] NOT NULL ,
	[language] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[preview] [int] NULL ,
	[send_new_version] [int] NULL ,
	[memo] [image] NULL ,
	[template_name] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[file_name] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[title] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[comments] [nvarchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[key_words] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[abstract] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[creation_date] [datetime] NOT NULL ,
	[modif_date] [datetime] NULL ,
	[parent_nrid] [decimal](15, 0) NULL ,
	[root_nrid] [decimal](15, 0) NULL ,
	[published] [int] NULL ,
	[num1] [decimal](25, 9) NULL ,
	[num2] [decimal](25, 9) NULL ,
	[num3] [decimal](25, 9) NULL ,
	[num4] [decimal](25, 9) NULL ,
	[num5] [decimal](25, 9) NULL ,
	[num6] [decimal](25, 9) NULL ,
	[num7] [decimal](25, 9) NULL ,
	[var1] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var2] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var3] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var4] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var5] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var6] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var7] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var8] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var9] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[var10] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
	[dat1] [datetime] NULL ,
	[dat2] [datetime] NULL ,
	[dat3] [datetime] NULL ,
	[merge_on_server] [int] NULL ,
	[dm2_nrid] [decimal](15, 0) NULL ,
	[full_view] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Et cette ligne "[memo] [image] NULL" confirme le résultat de la requête.

Déduction:

Cette colonne n'a pas d'attribut Collation, donc la collation à utiliser pour bien visualiser correctement les accents qui sont stocké dans le champ de type image est la collation du fichier texte de base.
Le soucis est que je ne peux pas connaitre celui-ci, simplement supposer que c'est un Latin1xxx, sauf que toute une multitude de Latin1 avec différentes variantes avec les CI/CS AI/AS ont été testé sans aucun succès.

Ces histoires de jeu de caractère sont vraiment pénible, j'ai le même soucis sur un site que je réalise en ce moment, j'ai des problèmes d'accents, que j'ai pu résoudre en transformant les accents en caractère spécial html(é par exemple) car je charge des fichiers dont je n'arrive pas à connaître le jeu de caractère, mais je dois générer un export csv de ma base et ces problèmes d'accents me poursuivent à ce moment la encore :p.

**SQLpro** · 17/07/2009, 15h02

Plus simplement pour la collation d'une colonne, utilisez la vue normalisée INFORMATION_SCHEMA.COLUMNS

Ensuite,

Résultat:

nomTable | nomColonne | nomCollationColonne
dm0 | memo | NULL

Un type "image" n'a pas de collation puisque c'est du binaire !!! Donc, votre problème nécessite une approche par tâtonnement. C'est d'ailleurs parfaitement normal, car visiblement votre base de données est modélisée de façon stupide en violant la première forme normale :

1NF : dans une relation, chaque attribut doit contenir une valeur atomique (c'est à dire que les valeurs ne peuvent pas être divisées en plusieurs sous-valeurs dépendant également individuellement de la clé primaire)

Or en ayant mélanger de l'image et du texte on est en violation de la première forme normale !!! Dès lors, la base s'avère inexploitable...

A +

**luunaz** · 20/07/2009, 09h10

D'accord je comprends bien.

Dernière question:
Existe-t-il un moyen de connaître le jeu de caractère utilisé à partir de la chaine extraite? Ou alors faut-il nécessairement tester tous les jeux de caractères? Car beaucoup de collation ont été testée sans succès, cela semble indémélable.

En tout cas, merci pour vos réponses, c'est beaucoup plus clair à présent.

**SQLpro** · 20/07/2009, 17h02

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

en particulier : CHARACTER_SET_NAME

A +

**luunaz** · 21/07/2009, 11h50

J'ai bien compris comment récupérer la collation d'une colonne mais là, je parlais du jeu de caractère de la chaîne extraire du champ de type IMAGE. Vu que le type IMAGE n'en a pas, je voulais savoir s'il y avait un moyen de le récupérer directement depuis la chaîne (Ca m'étonnerait que l'on puisse mais je préfère demander au cas où).

**elsuket** · 21/07/2009, 22h49

Non, cela est impossible

@++

**luunaz** · 22/07/2009, 09h04

Envoyé par elsuket

Non, cela est impossible

Je m'en doutais bien ...

Bon et bien vous avez répondu à toutes mes questions, même si ça ne résout pas, au final, mon problème au moins je le comprends mieux, c'est déjà ça

.

Je vous remercie bien

.

@++