Microsoft SQL Server
Funzioni di stringa

Osservazioni

Sinistra

Restituisce una sottostringa che inizia con il carattere più a sinistra di una stringa e fino alla lunghezza massima specificata.

parametri:

1. espressione del personaggio. L'espressione di carattere può essere di qualsiasi tipo di dati che può essere convertita implicitamente in varchar o nvarchar , ad eccezione di text o ntext
2. lunghezza massima. Un numero intero compreso tra 0 e bigint max bigint (9.223.372.036.854.775.807).
   Se il parametro lunghezza massima è negativo, verrà generato un errore.

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

Se la lunghezza massima è maggiore del numero di caratteri nella stringa, viene restituita la stringa dell'intero.

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

Destra

Restituisce una sottostringa che rappresenta la parte più corretta della stringa, con la lunghezza massima specificata.

parametri:

1. espressione del personaggio. L'espressione di carattere può essere di qualsiasi tipo di dati che può essere convertita implicitamente in varchar o nvarchar , ad eccezione di text o ntext
2. lunghezza massima. Un numero intero compreso tra 0 e bigint max bigint (9.223.372.036.854.775.807). Se il parametro lunghezza massima è negativo, verrà generato un errore.

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

Se la lunghezza massima è maggiore del numero di caratteri nella stringa, viene restituita la stringa dell'intero.

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

substring

Restituisce una sottostringa che inizia con il carattere che si trova nell'indice iniziale specificato e nella lunghezza massima specificata.

parametri:

1. Espressione di carattere L'espressione di carattere può essere di qualsiasi tipo di dati che può essere convertita implicitamente in varchar o nvarchar , ad eccezione di text o ntext .
2. Inizia indice. Un numero ( int o bigint ) che specifica l'indice iniziale della sottostringa richiesta. ( Nota: le stringhe nel server SQL sono indice di base 1, il che significa che il primo carattere della stringa è l'indice 1). Questo numero può essere inferiore a 1. In questo caso, se la somma dell'indice iniziale e della lunghezza massima è maggiore di 0, la stringa di ritorno sarà una stringa che inizia dal primo carattere dell'espressione di carattere e con la lunghezza di (indice iniziale + lunghezza massima - 1). Se è inferiore a 0, verrà restituita una stringa vuota.
3. Lunghezza massima. Un numero intero compreso tra 0 e bigint max bigint (9.223.372.036.854.775.807). Se il parametro lunghezza massima è negativo, verrà generato un errore.

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

Se la lunghezza massima + indice iniziale è maggiore del numero di caratteri nella stringa, viene restituita la stringa dell'intero.

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

Se l'indice iniziale è più grande del numero di caratteri nella stringa, viene restituita una stringa vuota.

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

ASCII

Restituisce un valore int che rappresenta il codice ASCII del carattere più a sinistra di una stringa.

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

Se la stringa è Unicode e il carattere più a sinistra non è ASCII ma è rappresentabile nelle regole di confronto correnti, è possibile restituire un valore superiore a 127:

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

Se la stringa è Unicode e il carattere più a sinistra non può essere rappresentato nelle regole di confronto correnti, viene restituito il valore int di 63: (che rappresenta il punto interrogativo in ASCII):

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

charIndex

Restituisce l'indice iniziale di una prima occorrenza di un'espressione stringa all'interno di un'altra espressione di stringa.

Lista dei parametri:

1. Stringa per trovare (fino a 8000 caratteri)
2. Stringa da cercare (qualsiasi tipo di dati di carattere valido e lunghezza, incluso binario)
3. (Opzionale) indice per iniziare. Un numero di tipo int o big int. Se omesso o meno di 1, la ricerca inizia all'inizio della stringa.

Se la stringa da cercare è varchar(max) , nvarchar(max) o varbinary(max) , la funzione CHARINDEX restituirà un valore bigint . Altrimenti, restituirà 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

carbonizzare

Restituisce un carattere rappresentato da un codice ASCII int.

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

Questo può essere usato per introdurre nuovi line / line feed CHAR(10) , carriage return CHAR(13) , ecc. Vedi AsciiTable.com come riferimento.

Se il valore dell'argomento non è compreso tra 0 e 255, la funzione CHAR restituisce NULL .
Il tipo di dati di ritorno della funzione CHAR è char(1)

Len

Restituisce il numero di caratteri di una stringa.
Nota: la funzione LEN ignora gli spazi finali:

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

Se la lunghezza, compresi gli spazi finali, è desiderata, ci sono diverse tecniche per raggiungere questo obiettivo, anche se ognuno ha i suoi svantaggi. Una tecnica è quella di aggiungere un singolo carattere alla stringa, e quindi utilizzare il LEN meno uno:

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

Lo svantaggio è se il tipo della variabile o colonna stringa è della lunghezza massima, l'append del carattere extra viene scartata e la lunghezza risultante non conterà ancora gli spazi finali. Per risolvere questo problema, la seguente versione modificata risolve il problema e fornisce i risultati corretti in tutti i casi a scapito di una piccola quantità di tempo di esecuzione aggiuntivo, e per questo (risultati corretti, anche con coppie surrogate e una ragionevole velocità di esecuzione) sembra essere la migliore tecnica da usare:

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

Un'altra tecnica consiste nell'utilizzare la funzione DATALENGTH .

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

È importante notare che DATALENGTH restituisce la lunghezza in byte della stringa in memoria. Questo sarà diverso per varchar vs nvarchar .

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

È possibile regolare per questo dividendo la lunghezza dei dati della stringa per la lunghezza dei dati di un singolo carattere (che deve essere dello stesso tipo). L'esempio seguente esegue questa operazione e gestisce anche il caso in cui la stringa di destinazione risulta vuota, evitando così una divisione per zero.

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

Anche questo, tuttavia, ha un problema in SQL Server 2012 e versioni successive. Produrrà risultati errati quando la stringa contiene coppie surrogate (alcuni caratteri possono occupare più byte di altri caratteri nella stessa stringa).

Un'altra tecnica consiste nell'usare REPLACE per convertire gli spazi in un carattere non spaziale e prendere la LEN del risultato. Ciò fornisce risultati corretti in tutti i casi, ma ha una velocità di esecuzione molto scarsa con stringhe lunghe.

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'

Inferiore

Restituisce un'espressione di carattere ( varchar o nvarchar ) dopo aver convertito tutti i caratteri maiuscoli in caratteri minuscoli.

parametri:

1. Espressione di carattere Qualsiasi espressione di carattere o dati binari che possa essere convertita implicitamente in 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'

Superiore

Restituisce un'espressione di carattere ( varchar o nvarchar ) dopo aver convertito tutti i caratteri minuscoli in maiuscoli.

parametri:

1. Espressione di carattere Qualsiasi espressione di carattere o dati binari che possa essere convertita implicitamente in 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

Restituisce un'espressione di carattere ( varchar o nvarchar ) dopo aver rimosso tutti gli spazi bianchi iniziali, cioè gli spazi bianchi da sinistra fino al primo carattere di spazio non bianco.

parametri:

1. espressione del personaggio. Qualsiasi espressione di carattere o dati binari che può essere convertita implicitamente in varcher , ad eccezione di text , ntext e image .

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

RTrim

Restituisce un'espressione di carattere ( varchar o nvarchar ) dopo aver rimosso tutti gli spazi bianchi finali, ovvero gli spazi dall'estremità destra della stringa fino al primo carattere spazio non vuoto a sinistra.

parametri:

1. espressione del personaggio. Qualsiasi espressione di carattere o dati binari che può essere convertita implicitamente in varcher , ad eccezione di text , ntext e image .

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

Unicode

Restituisce il valore intero che rappresenta il valore Unicode del primo carattere dell'espressione di input.

parametri:

1. Espressione di caratteri Unicode. Qualsiasi espressione valida nchar o nvarchar .

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

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

nchar

Restituisce i caratteri Unicode ( nchar(1) o nvarchar(2) ) corrispondenti all'argomento intero che riceve, come definito dallo standard Unicode.

parametri:

1. espressione intera. Qualsiasi espressione intera che è un numero positivo compreso tra 0 e 65535, o se le regole di confronto del database supportano il flag di carattere supplementare (CS), l'intervallo supportato è compreso tra 0 e 1114111. Se l'espressione intera non rientra in questo intervallo, null è restituito.

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

Inverso

Restituisce un valore stringa in ordine inverso.

parametri:

1. espressione di stringa. Qualsiasi stringa o dati binari che possono essere convertiti implicitamente in varchar .

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

PATINDEX

Restituisce la posizione iniziale della prima occorrenza di un modello specificato nell'espressione specificata.

parametri:

1. modello. Un'espressione di carattere contiene la sequenza da trovare. Limitato a una lunghezza massima di 8000 caratteri. I caratteri jolly ( % , _ ) possono essere utilizzati nel modello. Se il modello non inizia con un carattere jolly, può corrispondere solo a ciò che si trova all'inizio dell'espressione. Se non termina con un carattere jolly, può corrispondere solo a ciò che si trova alla fine dell'espressione.

2. espressione. Qualsiasi tipo di dati stringa.

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. 

Spazio

Restituisce una stringa ( varchar ) di spazi ripetuti.

parametri:

1. espressione intera. Qualsiasi espressione intera, fino a 8000. Se negativo, viene restituito un valore null . se 0, viene restituita una stringa vuota. (Per restituire una stringa più lunga di 8000 spazi, utilizzare Replica.

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

Replicare

Ripete un valore di stringa un numero specificato di volte.

parametri:

1. espressione di stringa. L'espressione di stringa può essere una stringa di caratteri o dati binari.
2. espressione intera. Qualsiasi tipo intero, incluso bigint . Se negativo, viene restituito un valore null . Se 0, viene restituita una stringa vuota.

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

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

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

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

Nota: se l'espressione stringa non è di tipo varchar(max) o nvarchar(max) , il valore restituito non supererà 8000 caratteri. La replica si interrompe prima di aggiungere la stringa che farà sì che il valore restituito superi tale limite:

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

Sostituire

Restituisce una stringa ( varchar o nvarchar ) in cui tutte le occorrenze di una sottostringa specificata vengono sostituite con un'altra sottostringa.

parametri:

1. espressione di stringa. Questa è la stringa che verrebbe cercata. Può essere un carattere o un tipo di dati binari.
2. modello. Questa è la stringa secondaria che verrebbe sostituita. Può essere un carattere o un tipo di dati binari. L'argomento pattern non può essere una stringa vuota.
3. sostituzione. Questa è la stringa secondaria che sostituisce la stringa secondaria del modello. Può essere un carattere o dati binari.

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

Gli appunti:

• Se l'espressione stringa non è di tipo varchar(max) o nvarchar(max) , la funzione replace tronca il valore restituito a 8.000 caratteri.
• Il tipo di dati restituiti dipende dai tipi di dati di input: restituisce nvarchar se uno dei valori di input è nvarchar o altrimenti varchar .
• Restituisce NULL se uno qualsiasi dei parametri di input è 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

Restituisce i dati dei caratteri ( varchar ) convertiti da dati numerici.

parametri:

1. espressione fluttuante. Un tipo di dati numerico approssimativo con un punto decimale.
2. lunghezza. opzionale. La lunghezza totale dell'espressione stringa che verrebbe restituita, comprese le cifre, il punto decimale e gli spazi iniziali (se necessario). Il valore predefinito è 10.
3. decimale. opzionale. Il numero di cifre a destra del punto decimale. Se superiore a 16, il risultato verrebbe troncato a sedici posti a destra del separatore decimale.

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

Restituisce una stringa Unicode circondata da delimitatori per renderla un identificatore delimitato di SQL Server valido.

parametri:

1. stringa di caratteri. Una stringa di dati Unicode, fino a 128 caratteri ( sysname ). Se una stringa di input è più lunga di 128 caratteri, la funzione restituisce null .
2. citare il carattere. Opzionale . Un singolo carattere da utilizzare come delimitatore. Può essere una virgoletta singola ( ' o ``), una parentesi sinistra o destra ( { , [ , ( , < o > , ) , ] , } ) o una virgoletta doppia ( " ). Qualsiasi altro valore restituirà null Il valore predefinito è parentesi quadre.

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

Restituisce un codice di quattro caratteri ( varchar ) per valutare la somiglianza fonetica di due stringhe.

parametri:

1. espressione del personaggio. Un'espressione alfanumerica di dati di carattere.

La funzione soundex crea un codice di quattro caratteri basato su come l'espressione del personaggio suonerebbe quando pronunciata. il primo carattere è la versione maiuscola del primo carattere del parametro, il resto 3 caratteri sono numeri che rappresentano le lettere nell'espressione (tranne a, e, i, o, u, h, w e y che vengono ignorati) .

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

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

Differenza

Restituisce un valore intero ( int ) che indica la differenza tra i valori soundex di due espressioni di carattere.

parametri:

1. espressione del personaggio 1.
2. espressione del personaggio 2.

Entrambi i parametri sono espressioni alfanumeriche di dati di carattere.

Il numero intero restituito è il numero di caratteri nei valori soundex dei parametri che sono gli stessi, quindi 4 significa che le espressioni sono molto simili e 0 significa che sono molto diverse.

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

Formato

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