Microsoft SQL Server
Fonctions de chaîne

Remarques

La gauche

Renvoie une sous-chaîne commençant par le caractère le plus à gauche d'une chaîne et jusqu'à la longueur maximale spécifiée.

Paramètres:

1. expression de caractère. L'expression de caractère peut être de tout type de données pouvant être implicitement converti en varchar ou nvarchar , sauf pour text ou ntext
2. longueur maximale. Un nombre entier compris entre 0 et bigint valeur max (9,223,372,036,854,775,807).
   Si le paramètre de longueur maximale est négatif, une erreur sera générée.

SELECT LEFT('This is my string', 4) -- result: 'This'

Si la longueur maximale est supérieure au nombre de caractères de la chaîne, la chaîne entière est renvoyée.

SELECT LEFT('This is my string', 50) -- result: 'This is my string'

Droite

Renvoie une sous-chaîne qui est la partie la plus à droite de la chaîne, avec la longueur maximale spécifiée.

Paramètres:

1. expression de caractère. L'expression de caractère peut être de tout type de données pouvant être implicitement converti en varchar ou nvarchar , sauf pour text ou ntext
2. longueur maximale. Un nombre entier compris entre 0 et bigint valeur max (9,223,372,036,854,775,807). Si le paramètre de longueur maximale est négatif, une erreur sera générée.

SELECT RIGHT('This is my string', 6) -- returns 'string'

Si la longueur maximale est supérieure au nombre de caractères de la chaîne, la chaîne entière est renvoyée.

SELECT RIGHT('This is my string', 50) -- returns 'This is my string'

Substring

Renvoie une sous-chaîne qui commence par le caractère qui se trouve dans l'index de démarrage spécifié et la longueur maximale spécifiée.

Paramètres:

1. Expression de caractère. L'expression de caractère peut être de n'importe quel type de données pouvant être implicitement converti en varchar ou nvarchar , sauf pour text ou ntext .
2. Index de départ. Un nombre ( int ou bigint ) qui spécifie l'index de démarrage de la sous-chaîne demandée. ( Remarque: les chaînes du serveur SQL sont des index de base 1, ce qui signifie que le premier caractère de la chaîne est l’index 1). Ce nombre peut être inférieur à 1. Dans ce cas, si la somme de l'index de départ et de la longueur maximale est supérieure à 0, la chaîne de retour serait une chaîne commençant par le premier caractère de l'expression + longueur max - 1). Si c'est moins que 0, une chaîne vide sera renvoyée.
3. Longueur maximale. Un nombre entier compris entre 0 et bigint valeur max (9,223,372,036,854,775,807). Si le paramètre de longueur maximale est négatif, une erreur sera générée.

SELECT SUBSTRING('This is my string', 6, 5) -- returns 'is my'

Si la longueur maximale + l'index de début est supérieur au nombre de caractères de la chaîne, la chaîne entière est renvoyée.

SELECT SUBSTRING('Hello World',1,100) -- returns 'Hello World'

Si l'index de démarrage est plus grand que le nombre de caractères de la chaîne, une chaîne vide est renvoyée.

SELECT SUBSTRING('Hello World',15,10) -- returns ''

ASCII

Renvoie une valeur int représentant le code ASCII du caractère le plus à gauche d'une chaîne.

SELECT ASCII('t') -- Returns 116
SELECT ASCII('T') -- Returns 84
SELECT ASCII('This') -- Returns 84

Si la chaîne est Unicode et que le caractère le plus à gauche n'est pas ASCII mais peut être représenté dans le classement actuel, une valeur supérieure à 127 peut être renvoyée:

SELECT ASCII(N'ï') -- returns 239 when `SERVERPROPERTY('COLLATION') = 'SQL_Latin1_General_CP1_CI_AS'`

Si la chaîne est Unicode et que le caractère le plus à gauche ne peut pas être représenté dans le classement actuel, la valeur int de 63 est renvoyée: (qui représente le point d'interrogation en ASCII):

SELECT ASCII(N'߷') -- returns 63
SELECT ASCII(nchar(2039)) -- returns 63

CharIndex

Renvoie l'index de début de la première occurrence d'une expression de chaîne dans une autre expression de chaîne.

Liste de paramètres:

1. Chaîne à trouver (jusqu'à 8000 caractères)
2. Chaîne à rechercher (tout type et longueur de données de caractère valide, y compris binaire)
3. (Facultatif) index pour démarrer. Un nombre de type int ou big int. Si omis ou moins de 1, la recherche commence au début de la chaîne.

Si la chaîne à rechercher est varchar(max) , nvarchar(max) ou varbinary(max) , la fonction CHARINDEX renverra une valeur bigint . Sinon, il retournera un int .

SELECT CHARINDEX('is', 'this is my string') -- returns 3
SELECT CHARINDEX('is', 'this is my string', 4) -- returns 6
SELECT CHARINDEX(' is', 'this is my string') -- returns 5

Carboniser

Retourne un caractère représenté par un code int ASCII.

SELECT CHAR(116) -- Returns 't'
SELECT CHAR(84)  -- Returns 'T'

Cela peut être utilisé pour introduire de nouvelles lignes / lignes CHAR(10) , le chariot retourne CHAR(13) , etc. Voir AsciiTable.com pour référence.

Si la valeur de l'argument n'est pas comprise entre 0 et 255, la fonction CHAR renvoie NULL .
Le type de données renvoyé par la fonction CHAR est char(1)

Len

Renvoie le nombre de caractères d'une chaîne.
Remarque: la fonction LEN ignore les espaces de fin:

SELECT LEN('My string'), -- returns 9
       LEN('My string   '), -- returns 9
       LEN('   My string') -- returns 12

Si la longueur, y compris les espaces de fuite, est souhaitée, il existe plusieurs techniques pour y parvenir, bien que chacune ait ses inconvénients. Une technique consiste à ajouter un seul caractère à la chaîne, puis à utiliser le LEN moins un:

DECLARE @str varchar(100) = 'My string   '
SELECT LEN(@str + 'x') - 1 -- returns 12

L'inconvénient est que si le type de la variable de chaîne ou de la colonne est de la longueur maximale, l'ajout du caractère supplémentaire est ignoré et la longueur résultante ne compte toujours pas les espaces de fin. Pour remédier à cela, la version modifiée suivante résout le problème et donne les résultats corrects dans tous les cas, au prix d'une petite durée d'exécution supplémentaire (résultats corrects, y compris avec des paires de substitution et une vitesse d'exécution raisonnable). semble être la meilleure technique à utiliser:

SELECT LEN(CONVERT(NVARCHAR(MAX), @str) + 'x') - 1

Une autre technique consiste à utiliser la fonction DATALENGTH .

DECLARE @str varchar(100) = 'My string   '
SELECT DATALENGTH(@str) -- returns 12

Il est important de noter que DATALENGTH renvoie la longueur en octets de la chaîne en mémoire. Ce sera différent pour varchar vs nvarchar .

DECLARE @str nvarchar(100) = 'My string   '
SELECT DATALENGTH(@str) -- returns 24

Vous pouvez ajuster cela en divisant la longueur de données de la chaîne par la longueur de données d'un seul caractère (qui doit être du même type). L'exemple ci-dessous le fait, et gère également le cas où la chaîne cible est vide, évitant ainsi une division par zéro.

DECLARE @str nvarchar(100) = 'My string   '
SELECT DATALENGTH(@str) / DATALENGTH(LEFT(LEFT(@str, 1) + 'x', 1)) -- returns 12

Même cela, cependant, a un problème dans SQL Server 2012 et supérieur. Il produira des résultats incorrects lorsque la chaîne contient des paires de substitution (certains caractères peuvent occuper plus d'octets que d'autres caractères dans la même chaîne).

Une autre technique consiste à utiliser REPLACE pour convertir des espaces en un caractère non-espace et prendre la LEN d' LEN du résultat. Cela donne des résultats corrects dans tous les cas, mais a une très faible vitesse d'exécution avec de longues chaînes.

Concat

SELECT CONCAT('This', ' is', ' my', ' string') -- returns 'This is my string'

SELECT CONCAT('This', NULL, ' is', ' my', ' string'), -- returns 'This is my string'
       'This' + NULL + ' is' + ' my' + ' string' -- returns NULL.

SELECT CONCAT('This', ' is my ', 3, 'rd string') -- returns 'This is my 3rd string'

DECLARE @Age INT=23;
SELECT CONCAT('Ram is ', @Age,' years old');  -- returns 'Ram is 23 years old'

SELECT 'This is the number ' + CAST(42 AS VARCHAR(5)) --returns 'This is the number 42'

Inférieur

Renvoie une expression de caractère ( varchar ou nvarchar ) après avoir converti tous les caractères majuscules en minuscules.

Paramètres:

1. Expression de caractère. Toute expression de caractère ou de données binaires pouvant être implicitement convertie en varchar .

SELECT LOWER('This IS my STRING') -- Returns 'this is my string'

DECLARE @String nchar(17) = N'This IS my STRING';
SELECT LOWER(@String) -- Returns 'this is my string'

Plus haut

Renvoie une expression de caractère ( varchar ou nvarchar ) après avoir converti tous les caractères minuscules en majuscules.

Paramètres:

1. Expression de caractère. Toute expression de caractère ou de données binaires pouvant être implicitement convertie en varchar .

SELECT UPPER('This IS my STRING') -- Returns 'THIS IS MY STRING'

DECLARE @String nchar(17) = N'This IS my STRING';
SELECT UPPER(@String) -- Returns 'THIS IS MY STRING'

LTrim

Renvoie une expression de caractère ( varchar ou nvarchar ) après avoir supprimé tous les espaces blancs en tête, c.-à-d. Les espaces blancs de gauche à droite du premier caractère non blanc.

Paramètres:

1. expression de caractère. Toute expression de données de caractère ou binaire pouvant être implicitement convertie en varcher , à l'exception de text , ntext et image .

SELECT LTRIM('    This is my string') -- Returns 'This is my string'

RTrim

Retourne une expression de caractère ( varchar ou nvarchar ) après avoir supprimé tous les espaces blancs à la fin, c'est-à-dire les espaces de l'extrémité droite de la chaîne jusqu'au premier caractère d'espacement non blanc à gauche.

Paramètres:

1. expression de caractère. Toute expression de données de caractère ou binaire pouvant être implicitement convertie en varcher , à l'exception de text , ntext et image .

SELECT RTRIM('This is my string     ') -- Returns 'This is my string'

Unicode

Renvoie la valeur entière représentant la valeur Unicode du premier caractère de l'expression d'entrée.

Paramètres:

1. Expression de caractères Unicode. Toute expression nchar ou nvarchar valide.

SELECT UNICODE(N'Ɛ') -- Returns 400

DECLARE @Unicode nvarchar(11) = N'Ɛ is a char'
SELECT UNICODE(@Unicode) -- Returns 400

NChar

Renvoie le ou les caractères Unicode ( nchar(1) ou nvarchar(2) ) correspondant à l'argument entier qu'il reçoit, tel que défini par la norme Unicode.

Paramètres:

1. expression entière. Toute expression entière qui est un nombre positif entre 0 et 65535, ou si le classement de la base de données prend en charge le caractère supplémentaire (CS) drapeau, la plage prise en charge est comprise entre 0 et 1114111. Si l'expression entière ne tombe pas dans cette plage, null est revenu.

SELECT NCHAR(257) -- Returns 'ā'
SELECT NCHAR(400) -- Returns 'Ɛ'

Sens inverse

Renvoie une valeur de chaîne dans l'ordre inverse.

Paramètres:

1. expression de chaîne. Toute chaîne ou donnée binaire pouvant être implicitement convertie en varchar .

Select REVERSE('Sql Server') -- Returns 'revreS lqS'

PatIndex

Renvoie la position de départ de la première occurrence du modèle spécifié dans l'expression spécifiée.

Paramètres:

1. modèle. Une expression de caractère contient la séquence à rechercher. Limité à une longueur maximale de 8000 caractères. Les caractères génériques ( % , _ ) peuvent être utilisés dans le motif. Si le motif ne commence pas par un caractère générique, il ne peut correspondre qu’au début de l’expression. S'il ne se termine pas par un caractère générique, il ne peut correspondre qu'à ce qui se trouve à la fin de l'expression.

2. expression. Tout type de données de chaîne.

SELECT PATINDEX('%ter%', 'interesting') -- Returns 3. 

SELECT PATINDEX('%t_r%t%', 'interesting') -- Returns 3. 

SELECT PATINDEX('ter%', 'interesting') -- Returns 0, since 'ter' is not at the start. 

SELECT PATINDEX('inter%', 'interesting') -- Returns 1. 

SELECT PATINDEX('%ing', 'interesting') -- Returns 9. 

Espace

Retourne une chaîne ( varchar ) d'espaces répétés.

Paramètres:

1. expression entière. Toute expression entière, jusqu'à 8000. Si la null est négative, la valeur null est renvoyée. si 0, une chaîne vide est renvoyée. (Pour retourner une chaîne plus longue que 8000 espaces, utilisez Replicate.

SELECT SPACE(-1) -- Returns NULL
SELECT SPACE(0)  -- Returns an empty string
SELECT SPACE(3)  -- Returns '   ' (a string containing 3 spaces)

Reproduire

Répète une valeur de chaîne un nombre de fois spécifié.

Paramètres:

1. expression de chaîne. L'expression de chaîne peut être une chaîne de caractères ou des données binaires.
2. expression entière. Tout type entier, y compris bigint . Si négatif, null est renvoyé. Si 0, une chaîne vide est renvoyée.

SELECT REPLICATE('a', -1)  -- Returns NULL

SELECT REPLICATE('a', 0)  -- Returns ''

SELECT REPLICATE('a', 5)  -- Returns 'aaaaa'

SELECT REPLICATE('Abc', 3) -- Returns 'AbcAbcAbc'

Remarque: Si l'expression de chaîne n'est pas de type varchar(max) ou nvarchar(max) , la valeur de retour ne dépassera pas 8 000 caractères. Replicate s'arrêtera avant d'ajouter la chaîne qui entraînera un dépassement de la valeur de retour:

SELECT LEN(REPLICATE('a b c d e f g h i j k l', 350)) -- Returns 7981

SELECT LEN(REPLICATE(cast('a b c d e f g h i j k l' as varchar(max)), 350)) -- Returns 8050

Remplacer

Renvoie une chaîne ( varchar ou nvarchar ) où toutes les occurrences d'une sous-chaîne spécifiée sont remplacées par une autre sous-chaîne.

Paramètres:

1. expression de chaîne. C'est la chaîne qui serait recherchée. Il peut s'agir d'un type de données caractère ou binaire.
2. modèle. C'est la sous-chaîne qui serait remplacée. Il peut s'agir d'un type de données caractère ou binaire. L'argument pattern ne peut pas être une chaîne vide.
3. remplacement. C'est la sous-chaîne qui remplacerait la sous-chaîne de motif. Cela peut être un caractère ou des données binaires.

SELECT REPLACE('This is my string', 'is', 'XX') -- Returns 'ThXX XX my string'.

Remarques:

• Si l'expression de chaîne n'est pas de type varchar(max) ou nvarchar(max) , la fonction replace tronque la valeur de retour à 8 000 caractères.
• Le type de données renvoyé dépend des types de données d'entrée - renvoie nvarchar si l'une des valeurs d'entrée est nvarchar ou varchar sinon.
• Renvoie NULL si l'un des paramètres d'entrée est NULL

String_Split

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

value
-----
Lorem
ipsum
dolor
sit
amet.

ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130

Str

Renvoie les données de caractères ( varchar ) converties à partir de données numériques.

Paramètres:

1. expression flottante. Un type de données numérique approximatif avec un point décimal.
2. longueur. optionnel. La longueur totale de l'expression de chaîne qui renverrait, y compris les chiffres, le point décimal et les espaces de début (si nécessaire). La valeur par défaut est 10.
3. décimal. optionnel. Le nombre de chiffres à droite du séparateur décimal. Si elle est supérieure à 16, le résultat sera tronqué à seize places à droite du point décimal.

SELECT STR(1.2) -- Returns '         1'

SELECT STR(1.2, 3) -- Returns '  1'

SELECT STR(1.2, 3, 2) -- Returns '1.2'

SELECT STR(1.2, 5, 2) -- Returns ' 1.20'

SELECT STR(1.2, 5, 5) -- Returns '1.200'

SELECT STR(1, 5, 2) -- Returns ' 1.00'

SELECT STR(1) -- Returns '         1'

Quotename

Renvoie une chaîne Unicode entourée de délimiteurs pour en faire un identificateur SQL Server délimité valide.

Paramètres:

1. chaîne de caractères. Chaîne de données Unicode, 128 caractères maximum ( sysname ). Si une chaîne de saisie dépasse 128 caractères, la fonction renvoie null .
2. personnage de citation. Facultatif Un seul caractère à utiliser comme délimiteur. Peut - être un guillemet simple ( ' ou ``), un crochet gauche ou à droite ( { , [ , ( , < ou > , ) , ] , } ) ou une double guillemet ( " ). Toute autre valeur renvoie null La valeur par défaut est entre crochets.

SELECT QUOTENAME('what''s my name?')      -- Returns [what's my name?]

SELECT QUOTENAME('what''s my name?', '[') -- Returns [what's my name?]
SELECT QUOTENAME('what''s my name?', ']') -- Returns [what's my name?]

SELECT QUOTENAME('what''s my name?', '''') -- Returns 'what''s my name?'

SELECT QUOTENAME('what''s my name?', '"') -- Returns "what's my name?"

SELECT QUOTENAME('what''s my name?', ')') -- Returns (what's my name?)
SELECT QUOTENAME('what''s my name?', '(') -- Returns (what's my name?)

SELECT QUOTENAME('what''s my name?', '<') -- Returns <what's my name?>
SELECT QUOTENAME('what''s my name?', '>') -- Returns <what's my name?>

SELECT QUOTENAME('what''s my name?', '{') -- Returns {what's my name?}
SELECT QUOTENAME('what''s my name?', '}') -- Returns {what's my name?}

SELECT QUOTENAME('what''s my name?', '`') -- Returns `what's my name?`

Soundex

Renvoie un code à quatre caractères ( varchar ) pour évaluer la similarité phonétique de deux chaînes.

Paramètres:

1. expression de caractère. Une expression alphanumérique de données de caractères.

La fonction soundex crée un code à quatre caractères basé sur la manière dont l'expression des caractères retentirait au moment de son utilisation. le premier caractère est la version majuscule du premier caractère du paramètre, les 3 autres sont des nombres représentant les lettres de l'expression (sauf a, e, i, o, u, h, w et y sont ignorés) .

SELECT SOUNDEX ('Smith') -- Returns 'S530'

SELECT SOUNDEX ('Smythe') -- Returns 'S530'

Différence

Renvoie une valeur entière ( int ) qui indique la différence entre les valeurs soundex de deux expressions de caractère.

Paramètres:

1. expression de caractère 1.
2. expression de caractère 2.

Les deux paramètres sont des expressions alphanumériques des données de caractère.

L'entier retourné est le nombre de caractères dans les valeurs soundex des paramètres identiques, donc 4 signifie que les expressions sont très similaires et 0 signifie qu'elles sont très différentes.

SELECT  SOUNDEX('Green'),  -- G650
        SOUNDEX('Greene'),  -- G650
        DIFFERENCE('Green','Greene') -- Returns 4
        
SELECT  SOUNDEX('Blotchet-Halls'),  -- B432
        SOUNDEX('Greene'),  -- G650
        DIFFERENCE('Blotchet-Halls', 'Greene') -- Returns 0

Format

DECLARE @d DATETIME = '2016-07-31';  

SELECT 
    FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' -- Returns '7/31/2016'
   ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31/07/2016'
   ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' -- Returns '31.07.2016'
   ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Returns '2016/7/31'
   ,FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' -- Returns 'Sunday, July 31, 2016'
   ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31 July 2016'
   ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' -- Returns 'Sonntag, 31. Juli 2016'

SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' -- Returns '31/07/2016'
      ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result' -- Returns '123-45-6789',
      ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') AS 'US' -- Returns 'Sunday, July 31, 2016 12:00:00 AM'
      ,FORMAT( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN') AS 'Hindi' -- Returns रविवार, जुलाई 31, 2016 12:00:00 पूर्वाह्न
      ,FORMAT ( @d, 'dddd', 'en-US' )  AS 'US' -- Returns 'Sunday'
      ,FORMAT ( @d, 'dddd', 'hi-IN' )  AS 'Hindi' -- Returns 'रविवार'

DECLARE @Price1 INT = 40
SELECT FORMAT(@Price1,'c','en-US') AS 'CURRENCY IN US Culture' -- Returns '$40.00'      
       ,FORMAT(@Price1,'c','de-DE') AS 'CURRENCY IN GERMAN Culture' -- Returns '40,00 €'

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT FORMAT( @Price, 'C') AS 'Default', -- Returns '$40.36'
       FORMAT( @Price, 'C0') AS 'With 0 Decimal', -- Returns '$40'
       FORMAT( @Price, 'C1') AS 'With 1 Decimal', -- Returns '$40.4'
       FORMAT( @Price, 'C2') AS 'With 2 Decimal', -- Returns '$40.36'

   DECLARE @Percentage float = 0.35674
   SELECT FORMAT( @Percentage, 'P') AS '% Default', -- Returns '35.67 %'
   FORMAT( @Percentage, 'P0') AS '% With 0 Decimal', -- Returns '36 %'
   FORMAT( @Percentage, 'P1') AS '% with 1 Decimal'  -- Returns '35.7 %'

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US', -- Returns '454,545.39'
FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA', -- Returns '4,54,545.39'
FORMAT( @Number, '#.0')     AS 'With 1 Decimal', -- Returns '454545.4'
FORMAT( @Number, '#.00')    AS 'With 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal', -- Returns '454,545.39'
FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '000000000') AS 'Left-padded to nine digits' -- Returns '000454545'

Category         Type             .Net type
-------------------------------------------
Numeric          bigint           Int64
Numeric          int              Int32
Numeric          smallint         Int16
Numeric          tinyint          Byte
Numeric          decimal          SqlDecimal
Numeric          numeric          SqlDecimal
Numeric          float            Double
Numeric          real             Single
Numeric          smallmoney       Decimal
Numeric          money            Decimal
Date and Time    date             DateTime
Date and Time    time             TimeSpan
Date and Time    datetime         DateTime
Date and Time    smalldatetime    DateTime
Date and Time    datetime2        DateTime
Date and Time    datetimeoffset   DateTimeOffset

String_escape

SELECT STRING_ESCAPE('\   /  
\\    "     ', 'json') -- returns '\\\t\/\n\\\\\t\"\t'

Special character    Encoded sequence
-------------------------------------
Quotation mark (")   \"
Reverse solidus (\)  \\
Solidus (/)          \/
Backspace            \b
Form feed            \f
New line             \n
Carriage return      \r
Horizontal tab       \t


Control character    Encoded sequence
------------------------------------
CHAR(0)            \u0000
CHAR(1)            \u0001
...                ...
CHAR(31)           \u001f