Cet article est un effort pour discuter des fonctions SQL Cast et SQL Convert comme une suite aux articles précédents, dans lesquels nous avons abordé plusieurs astuces SQL telles que SQL Date, SQL Coalesce, SQL Union, SQL Join, SQL Like, SQL String etc.
Parfois, nous avons besoin de convertir des données entre différents types de données SQL. En plus de travailler avec les données, il existe certaines fonctions intégrées peuvent être utilisées pour convertir les données. Regardons donc de plus près les fonctions de conversion SQL CAST et SQL CONVERT en détail.
Introduction
Pour effectuer des opérations ou des comparaisons ou des transformations entre des données dans une base de données SQL Server, les types de données SQL de ces valeurs doivent correspondre. Lorsque les types de données SQL sont différents, ils passent par un processus appelé » type-casting « . La conversion des types de données SQL, dans ce processus, peut être implicite ou explicite.
Les fonctions de conversion de type de données couramment utilisées spécialement pour répondre aux normes de données des objets ou systèmes cibles. Nous sommes dans le monde traitant des données hétérogènes.
Note : Un parcours des concepts de haut niveau de la gestion des données est abordé dans cet article Fonctions de chaîne SQL pour le brassage (Wrangling) des données.
Parfois, les types de données des données doivent être convertis en d’autres types de données pour des calculs ou des transformations ou des processus ou pour répondre aux formats de données de destination. Par exemple, lorsque nous multiplions des données de type décimal avec un nombre entier, les données subissent une transformation interne et convertissent implicitement un nombre entier en type de données décimal et le résultat est une donnée décimale.
Conversion SQL implicite et explicite
Lorsque nous traitons deux valeurs qui sont de même nature mais de types de données différents, en coulisse, le moteur de base de données convertit les valeurs de type de données inférieur en type de données supérieur avant de pouvoir poursuivre le calcul. Ce type de conversion est connu sous le nom de conversion implicite. D’autre part, nous avons des conversions explicites où vous appelez une fonction SQL CAST ou SQL CONVERT pour changer le type de données. Vous pouvez vous référer à l’article Aperçu du format de date SQL ; fonction SQL DateDiff, fonction SQL DateAdd et plus pour des exemples.
Syntaxe :
|
1
2
… 3
. 4
|
CAST
CAST ( exp AS datatype )
CONVERT
CONVERT ( datatype , expression )
|
exp
Définit l’argument d’expression valide
datatype
Cela donne les détails du type de données cible
len
Cela a spécifié la longueur du type de données cible. Il s’agit d’un paramètre facultatif par défaut, la valeur de la longueur est fixée à 30
style
Il s’agit d’une valeur entière utilisée avec la fonction SQL CONVERT pour traduire l’expression en sortie souhaitée
Tableau de conversion des données SQL Convert et Cast
Le tableau de conversion suivant est une fiche de référence rapide pour la conversion des données de type Cast. tableau de conversion est une fiche de référence rapide pour la conversion des types de données
Exemple 1 : Conversion SQL Implicite avec des nombres
Dans l’exemple suivant, on additionne deux nombres aléatoires 5 et 212345 et on affiche les résultats.
|
1
2
|
DECLARE @a int=5, @b bigint=212345
Select @a, @b, @a+@b
|
On constate que les deux valeurs sont de types de données différents, le moteur de base de données a implicitement converti la valeur int en bigint avant de faire l’addition. Et ensuite, il a pu traiter l’opération d’addition car elles deviennent le même type de données.
Exemple 2 : Conversion implicite SQL avec des caractères
Dans l’exemple suivant, nous allons ajouter deux types de données caractères. Créons les variables @a type de données caractère et attribuons une valeur ‘5’ et @b avec une valeur ‘2’.
|
1
2
|
DECLARE @a char=’5′, @b char=’2′
Select @a, @b, @a+@b
|
Dans la sortie, premièrement, nous obtenons les valeurs des variables ‘a’ et ‘b’, deuxièmement et le résultat du calcul qui est les deux valeurs ensemble. Les valeurs de caractères étant en place, le moteur de base de données a implicitement décidé de concaténer les deux valeurs et d’afficher 52 comme sortie du calcul.
Exemple 3 : Comment effectuer une conversion explicite à l’aide de SQL Cast
Dans l’exemple suivant, nous allons voir comment forcer la conversion d’un type de données à un autre. Prenons un exemple ci-dessus et convertissons les valeurs d’entrée en type numérique, puis ajoutons ces valeurs.
|
1
2
|
DECLARE @a char=’5′, @b char=’2′
SeLECT @a,@b, cast(@a as int)+ cast(@b as int)
|
La fonction SQL CAST prend deux arguments. D’abord, les données que nous voulons traiter, dans ce cas, les données sont ‘5’ et ‘2’ et sont du champ char, et ensuite comment vous voulez les traiter, ou comment nous voulons les CAST. Dans ce cas, vous voulez le CAST comme un champ entier.
Dans la sortie, vous remarquez que les valeurs de caractères d’entrée sont converties et aussi ajouté ces valeurs et le résultat est de type entier.
Exemple 4 : Gestion avancée des erreurs à l’aide de TRY_CAST
Dans l’exemple suivant, nous allons disséquer davantage les valeurs des types de données à l’aide de la fonction SQL cast et de la fonction SQL try_cast.
Dans le SQL suivant, la valeur de la chaîne ‘123’ est convertie en BIGINT. Elle a pu être convertie en raison de la nature des données. Examinons le deuxième exemple, la valeur de chaîne ‘xyz’ est utilisée dans l’expression de la fonction SQL cast. Lorsque vous faites cela, vous verrez une erreur indiquant « Error Converting data type varchar to bigint ». Lorsque vous effectuez l’analyse de données avec diverses sources de données, nous obtenons généralement ce type de valeurs dans les données sources. Afin de gérer ce type de scénario, vous pouvez utiliser la fonction SQL try_cast. Dans la troisième ligne de SQL, nous passons la chaîne de caractères comme valeur d’entrée, après exécution, nous n’avons pas reçu d’erreur mais nous obtenons une valeur NULL en sortie. La valeur NULL peut être simplifiée davantage en utilisant la fonction SQL ISNULL ou la fonction SQL coalesce.
Note : vous pouvez vous référer à l’article Utilisation de la fonction SQL Coalesce dans SQL Server pour plus d’informations.
|
1
2
3
4
5
6
.. 7
|
Select TRY_CAST(‘123’ AS BIGINT) ;
GO
SELECT CAST(‘xyz’ AS BIGINT) ;
GO
SELECT TRY_CAST(‘xyz’ AS BIGINT) ;
GO
SELECT ISNULL(TRY_CAST(‘xyz’ AS BIGINT), 99) ;
|
Exemple 5 : Code de style expliqué dans les fonctions CONVERT
L’exemple suivant convertit le champ hiredate en différents styles disponibles de la fonction de conversion SQL. La fonction de conversion SQL commence d’abord par un type de données, et une virgule, puis est suivie d’une expression. Avec les dates, nous avons également un autre argument que nous pouvons fournir, qui s’appelle un code de style SQL. Vous pouvez en obtenir la liste dans l’article Documentation technique CAST et CONVERT (Transact-SQL).
|
1
2
3
4
5
6
7
8
9
|
SELECT hiredate,
CONVERT(CHAR(50), hiredate),
CONVERT(CHAR(50), hiredate, 1) ‘S-1’,
CONVERT(CHAR(50), hiredate, 101) ‘s-101’,
CONVERT(CHAR(50), hiredate, 102) ‘s-102’,
CONVERT(CHAR(50), hiredate, 103) ‘s-103’,
CONVERT(CHAR(50), hiredate, 104) ‘s-104’,
CONVERT(CHAR(50), hiredate, 105) ‘s-105’
FROM HumanResources.Employee ;
|
Exécutons le SQL susmentionné. Maintenant, nous pouvons voir les différents résultats qui sont affichés en utilisant les paramètres de style dans la fonction de conversion SQL. Vous pouvez voir que le champ hiredate convertit le champ de date en différents styles.
Exemple 6 : Les différences et similitudes entre CAST et CONVERT
Dans l’exemple suivant, vous pouvez voir que l’utilisation de CAST et CONVERT dans le T-SQL suivant pour comparer les temps d’exécution.
Exécutons le T-.SQL pour utiliser la fonction SQL CAST
|
1
2
3
4
5
6
7
8
9
10
11
|
SET STATISTICS TIME ON
WITH temp
AS (SELECT CAST(GETDATE() AS DATE) AS cast_date,
0 AS n
UNION ALL
Select CAST(GETDATE() + n AS DATE) AS cast_date,
n + 1
FROM temp
WHERE n < ; 1000000)
Select MAX(cast_date)
De temp OPTION(MAXRECURSION 0) ;
|
Maintenant, exécutez le même T-SQL avec la fonction CONVERT SQL en place.
|
1
2
3
4
5
6
7
8
9
10
11
|
SET STATISTICS TIME ON
WITH temp
AS (SELECT CONVERT(DATE, GETDATE(), 20) AS convert_date,
0 AS n
UNION ALL
SELECT CONVERT(DATE, GETDATE() + n, 20) AS convert_date,
n + 1
From temp
WHERE n < ; 1000000)
Select MAX(convert_date)
De temp OPTION(MAXRECURSION 0) ;
|
Conseils rapides :
- CAST est purement une norme ANSI-SQL. Mais, CONVERT est une fonction spécifique à SQL Server comme nous avons to_char ou to_date dans Oracle
- CAST est majoritairement disponible dans tous les produits de base de données en raison de sa portabilité et de sa convivialité
- Il n’y aura pas de différence majeure en termes d’exécution des requêtes entre les fonctions SQL Cast et SQL Convert. Vous pouvez voir une légère différence dans les temps d’exécution cela est dû à la conversion interne de SQL CAST à sa fonction native SQL CONVERT mais la fonction CONVERT est livrée avec une option « Style-code » pour dériver diverses combinaisons de date et heure, décimales et valeurs monétaires. Dans tous les cas, la fonction SQL CONVERT fonctionne légèrement mieux que la fonction SQL CAST
Exemple 7 : code de style dans le calcul à l’aide de CONVERT
Dans cet exemple, vous allez lister le champ taux de la table « HumanResources.EmployeeHistory » de la base de données Adventureworks2016. La deuxième colonne dans le SQL utilise la fonction SQL CONVERT avec le type de données char(10) et l’expression rate data le multiplier par 1000.
Le premier SQL, le code de style n’est pas utilisé mais pour le deuxième et le troisième SQL, le code de style est défini à 1.
|
1
2
3
4
5
6
7
8
9
|
Sélectionner les 3 taux les plus élevés,
CONVERT(CHAR(10), taux * 1000) AS TextPrice
FROM HumanResources.EmployeePayHistory ;
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000, 0) AS TextPrice
FROM HumanResources.EmployeePayHistory ;
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000, 1) AS TextPrice
FROM HumanResources.EmployeePayHistory ;
|
Dans la sortie, nous pouvons voir que la requête avec le code de style de 1 renvoie une sortie texte avec un séparateur de virgule à la position des milliers. La requête avec le code de style 0 renvoie une sortie texte sans séparateur de virgule.
Wrap Up
Jusqu’ici, nous avons parlé des fonctions SQL cast, SQL try_cast et SQL convert et de la façon dont vous pouvez convertir les chaînes de caractères, les entiers et les valeurs de date et d’heure. Nous avons également examiné la fonction CONVERT SQL avec le code de style. Nous avons parcouru quelques exemples de fonctions de conversion SQL implicites et explicites. Et nous avons compris la différence entre les fonctions SQL CAST et SQL CONVERT. Vous voyez également qu’une expression conditionnelle est utilisée avec la fonction SQL ISNULL avec la fonction SQL try_cast, et encore une fois, si j’avais fait cela sans utiliser la partie try, cela aurait échoué au lieu de retourner une valeur. C’est ainsi que vous pouvez couler des valeurs, ce qui est assez standard, avec ce niveau supplémentaire, et aussi et les erreurs plus gracieusement.
C’est tout pour le moment… J’espère que vous avez apprécié la lecture de cet article. N’hésitez pas à faire des commentaires ci-dessous.
- Auteur
- Postages récents
Prashanth JayaramMa spécialité réside dans la conception & la mise en œuvre de solutions de haute disponibilité et la migration de DB multiplateforme. Les technologies sur lesquelles je travaille actuellement sont SQL Server, PowerShell, Oracle et MongoDB.
Voir tous les messages de Prashanth Jayaram
- Un aperçu rapide de l’audit de base de données en SQL – 28 janvier, 2021
- Comment configurer Azure Data Sync entre les bases de données Azure SQL et le serveur SQL sur site – 20 janvier 2021
- Comment effectuer des opérations d’importation/exportation de bases de données Azure SQL à l’aide de PowerShell – 14 janvier 2021
.