Microsoft SQL Server
Strängfunktioner

Anmärkningar

Vänster

Returnerar en understräng som börjar med vänster mest rött av en sträng och upp till den angivna maximala längden.

parametrar:

1. karaktärsuttryck. Teckenuttrycket kan vara av vilken datatyp som helst som kan implicit konverteras till varchar eller nvarchar , med undantag för text eller ntext
2. Maxlängd. Ett heltal mellan 0 och bigint (9 223 372 036 854 775 807).
   Om maxlängdsparametern är negativ, kommer ett fel att tas upp.

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

Om maxlängden är mer än antalet tecken i strängen returneras entersträngen.

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

Rätt

Returnerar en understräng som är den högra delen av strängen, med den angivna maxlängden.

parametrar:

1. karaktärsuttryck. Teckenuttrycket kan vara av vilken datatyp som helst som kan implicit konverteras till varchar eller nvarchar , med undantag för text eller ntext
2. Maxlängd. Ett heltal mellan 0 och bigint (9 223 372 036 854 775 807). Om maxlängdsparametern är negativ, kommer ett fel att tas upp.

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

Om maxlängden är mer än antalet tecken i strängen returneras entersträngen.

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

träng

Returnerar en substring som börjar med den karaktär som finns i det angivna startindexet och den angivna maxlängden.

parametrar:

1. Teckenuttryck. Teckenuttrycket kan vara av valfri datatyp som implicit kan konverteras till varchar eller nvarchar , med undantag för text eller ntext .
2. Starta index. Ett nummer ( int eller bigint ) som anger startindex för den begärda substring. ( Obs: strängar i sql-server är bas 1-index, vilket betyder att strängets första tecken är index 1). Detta antal kan vara mindre än 1. I detta fall, om summan av startindex och maxlängd är större än 0, skulle retursträngen vara en sträng som börjar från den första karaktären i teckenuttrycket och med längden på (startindex + max längd - 1). Om det är mindre än 0, skulle en tom sträng returneras.
3. Maxlängd. Ett heltal mellan 0 och bigint (9 223 372 036 854 775 807). Om maxlängdsparametern är negativ, kommer ett fel att tas upp.

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

Om maxlängden + startindex är mer än antalet tecken i strängen, returneras entiersträngen.

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

Om startindex är större kommer antalet tecken i strängen att returneras en tom sträng.

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

ASCII

Returnerar ett int-värde som representerar ASCII-koden för en sträng till vänster.

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

Om strängen är Unicode och det längsta tecknet till vänster inte är ASCII men kan representeras i den aktuella samlingen kan ett värde större än 127 returneras:

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

Om strängen är Unicode och det vänstra tecknet inte kan representeras i den aktuella samlingen, returneras int-värdet på 63: (som representerar frågetecken i ASCII):

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

charIndex

Returnerar startindex för en första förekomst av stränguttryck i ett annat stränguttryck.

Parametrar lista:

1. Sträng att hitta (upp till 8000 karaktärer)
2. Sträng för sökning (valfri teckendatatyp och längd, inklusive binär)
3. (Valfritt) index för att starta. Ett antal typ int eller stora int. Om utelämnad eller mindre än 1 börjar sökningen i början av strängen.

Om strängen att söka är varchar(max) , nvarchar(max) eller varbinary(max) kommer CHARINDEX funktionen att returnera ett bigint värde. Annars kommer det att returnera ett 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

Röding

Returnerar en karaktär representerad av en int ASCII-kod.

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

Detta kan användas för att introducera ny linje / CHAR(10) , vagn returnerar CHAR(13) , etc. Se AsciiTable.com för referens.

Om argumentvärdet inte är mellan 0 och 255 returnerar CHAR-funktionen NULL .
CHAR funktionen är char(1)

Len

Returnerar antalet tecken i en sträng.
Obs: LEN funktionen ignorerar efterföljande utrymmen:

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

Om längden inklusive släputrymmen önskas finns det flera tekniker för att uppnå detta, även om var och en har sina nackdelar. En teknik är att lägga till ett enda tecken i strängen och sedan använda LEN minus en:

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

Nackdelen med detta är om typen av strängvariabeln eller kolumnen är av maximal längd, tillägget av det extra tecknet kasseras och den resulterande längden fortfarande inte räknar efterföljande utrymmen. För att hantera det löser följande modifierade version problemet och ger i alla fall rätt resultat på bekostnad av en liten mängd extra utförande tid, och på grund av detta (korrekta resultat, inklusive med surrogatpar, och rimlig exekveringshastighet) verkar vara den bästa tekniken att använda:

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

En annan teknik är att använda DATALENGTH funktionen.

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

Det är dock viktigt att notera att DATALENGTH returnerar längden i byte på strängen i minnet. Detta kommer att vara annorlunda för varchar kontra nvarchar .

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

Du kan justera för detta genom att dela strängens datalängd med datalängden för ett enda tecken (som måste vara av samma typ). Exemplet nedan gör detta och hanterar också fallet där målsträngen råkar vara tom och därmed undvika en klyftan med noll.

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

Även detta har dock ett problem i SQL Server 2012 och senare. Det ger felaktiga resultat när strängen innehåller surrogatpar (vissa tecken kan uppta fler byte än andra tecken i samma sträng).

En annan teknik är att använda REPLACE att konvertera mellanslag till ett icke-rymdkaraktär och ta LEN på resultatet. Detta ger korrekta resultat i alla fall, men har mycket dålig körhastighet med långa strängar.

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'

Lägre

Returnerar ett varchar ( varchar eller nvarchar ) efter att alla stora tecken har konverterats till små bokstäver.

parametrar:

1. Teckenuttryck. Alla uttryck för karaktär eller binära data som implicit kan konverteras till 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'

Övre

Returnerar ett varchar ( varchar eller nvarchar ) efter att alla små bokstäver har konverterats till versaler.

parametrar:

1. Teckenuttryck. Alla uttryck för karaktär eller binära data som implicit kan konverteras till 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

Returnerar ett varchar ( varchar eller nvarchar ) efter att alla ledande vita mellanslag har nvarchar bort, dvs vita mellanrum från vänster till det första icke-vita mellanslagstecknet.

parametrar:

1. karaktärsuttryck. Alla uttryck för karaktär eller binära data som implicit kan konverteras till varcher , utom text , ntext och image .

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

RTRIM

Returnerar ett varchar ( varchar eller nvarchar ) efter att alla bakre vita mellanrum har nvarchar bort, dvs mellanrum från höger ände av strängen tills det första icke-vita mellanrumstecknet till vänster.

parametrar:

1. karaktärsuttryck. Alla uttryck för karaktär eller binära data som implicit kan konverteras till varcher , utom text , ntext och image .

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

Unicode

Returnerar heltalvärdet som representerar Unicode-värdet för det första tecknet i inmatningsuttrycket.

parametrar:

1. Unicode-karaktärsuttryck. Alla giltiga nchar eller nvarchar uttryck.

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

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

nchar

Returnerar Unicode-karaktär (er) ( nchar(1) eller nvarchar(2) ) motsvarande heltalargumentet som det får, enligt definitionen av Unicode-standarden.

parametrar:

1. heltal uttryck. Alla heltalsuttryck som är ett positivt tal mellan 0 och 65535, eller om databasens sammansättning stöder flagg med kompletterande karaktär (CS), är det stödda intervallet mellan 0 och 1114111. Om heltaluttrycket inte faller inom detta område är null returnerad.

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

Omvänd

Returnerar ett strängvärde i omvänd ordning.

parametrar:

1. stränguttryck. Alla strängar eller binära data som implicit kan konverteras till varchar .

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

PatIndex

Returnerar startpositionen för den första förekomsten av ett specificerat mönster i det angivna uttrycket.

parametrar:

1. mönster. Ett teckenuttryck den innehåller sekvensen som ska hittas. Begränsad till en maximal längd på 8000 kar. Jokertecken ( % , _ ) kan användas i mönstret. Om mönstret inte börjar med ett jokertecken kan det bara matcha vad som är i början av uttrycket. Om det inte slutar med ett jokertecken kan det bara matcha vad som är i slutet av uttrycket.

2. uttryck. Alla strängdatatyper.

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. 

Plats

Returnerar en sträng ( varchar ) med upprepade mellanslag.

parametrar:

1. heltal uttryck. Alla heltaluttryck, upp till 8000. Om negativt returneras null . om 0 returneras en tom sträng. (För att returnera en sträng längre än 8000 mellanslag, använd Replikera.

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

Återskapa

Upprepar ett strängvärde ett angivet antal gånger.

parametrar:

1. stränguttryck. Stränguttryck kan vara en teckensträng eller binär data.
2. heltal uttryck. Varje heltalstyp, inklusive bigint . Om negativt returneras null . Om 0 returneras en tom sträng.

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

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

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

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

Obs: Om varchar(max) inte är av typen varchar(max) eller nvarchar(max) kommer returvärdet inte att överstiga 8000 karaktärer. Replikering kommer att stoppas innan du lägger till strängen som gör att returvärdet överskrider den gränsen:

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

Byta ut

Returnerar en sträng ( varchar eller nvarchar ) där alla förekomster av en specificerad substräng ersätts med en annan substräng.

parametrar:

1. stränguttryck. Detta är strängen som skulle sökas. Det kan vara ett tecken eller binär datatyp.
2. mönster. Detta är den substräng som skulle ersättas. Det kan vara ett tecken eller binär datatyp. Mönsterargumentet kan inte vara en tom sträng.
3. ersättning. Detta är den understrängen som skulle ersätta mönsterundersträngen. Det kan vara ett tecken eller binär data.

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

Anmärkningar:

• Om stränguttryck är inte av typen varchar(max) eller nvarchar(max) , den replace funktions trunkat returvärdet på 8000 tecken.
• Returdatatyp beror på ingångsdatatyper - returnerar nvarchar om ett av ingångsvärdena är nvarchar eller varchar annars.
• Returnera NULL om någon av ingångsparametrarna är 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

Returnerar varchar ( varchar ) konverterade från numeriska data.

parametrar:

1. flytande uttryck. En ungefärlig numerisk datatyp med en decimal.
2. längd. valfri. Den totala längden på stränguttrycket som skulle återgå, inklusive siffror, decimalpunkt och ledande mellanslag (om det behövs). Standardvärdet är 10.
3. decimal. valfri. Antalet siffror till höger om decimalpunkten. Om högre än 16, skulle resultatet avkortas till sexton platser till höger om decimalpunkten.

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

Returnerar en Unicode-sträng omgiven av avgränsare för att göra den till en giltig SQL Server-avgränsad identifierare.

parametrar:

1. teckensträng. En sträng med Unicode-data, upp till 128 tecken ( sysname ). Om en ingångsträng är längre än 128 tecken returnerar funktionen null .
2. citat karaktär. Valfritt . En enda karaktär att använda som avgränsare. Kan vara ett enda citattecken ( ' eller `'), en vänster eller höger konsol ( { , [ , ( , < eller > , ) , ] , } ) eller ett dubbel citattecken ( " ). Alla andra värden kommer att returnera noll Standardvärdet är fyrkantiga parenteser.

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

Returnerar en kod med fyra tecken ( varchar ) för att utvärdera den fonetiska likheten mellan två strängar.

parametrar:

1. karaktärsuttryck. Ett alfanumeriskt uttryck för karaktärsdata.

Soundex-funktionen skapar en fyra-teckenkod som bygger på hur teckenuttrycket skulle låta när det talas. den första karaktären är storversionen av parameterns första tecken, resten 3 tecken är siffror som representerar bokstäverna i uttrycket (förutom a, e, i, o, u, h, w och y som ignoreras) .

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

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

Skillnad

Returnerar ett heltal ( int ) -värde som indikerar skillnaden mellan soundex-värdena för två teckenuttryck.

parametrar:

1. karaktärsuttryck 1.
2. karaktärsuttryck 2.

Båda parametrarna är alfanumeriska uttryck för karaktärsdata.

Det heltal som returneras är antalet tecken i soundex-värdena för parametrarna som är desamma, så 4 betyder att uttrycka är mycket lika och 0 betyder att de är mycket olika.

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

Formatera

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