Funcțiile șirului SQL găsesc apariția. Funcții de șir SQL

Continuăm să studiem limbajul de interogare SQL și astăzi vom vorbi despre Funcții de șir SQL... Vom acoperi funcțiile de șir de bază și frecvent utilizate, cum ar fi: LOWER, LTRIM, INLOCUIEși altele, vom lua în considerare totul, desigur, prin exemple.

SELEGE numele || prenumele CA FIO DIN tabel

Sau, pentru a vă separa cu un spațiu, introduceți

SELEGE numele || "" || prenumele CA FIO DIN tabel

acestea. două bare verticale combină două coloane într-una și, pentru a le separa cu un spațiu, am pus un spațiu între ele ( se poate folosi orice caracter, cum ar fi liniuță sau colon) în apostrofe și, de asemenea, combinate cu două bare verticale ( Transact-SQL folosește + în loc de două bare verticale).

Funcția INITCAP

Urmează o funcție foarte utilă, INITCAP- care returnează valoarea într-un șir în care fiecare cuvânt începe cu o literă mare și continuă cu litere mici. Acest lucru este necesar dacă nu respectați regulile de completare dintr-o coloană sau alta și pentru a afișa totul într-un formular frumos, puteți utiliza această funcție, de exemplu, în tabelul dvs. înregistrările din coloana nume sunt ale următoarea formă: IVAN ivanov sau petr Petrov, utilizați această funcție.

SELECTAȚI INITCAP (nume) CA FIO DIN tabel

Și o veți obține așa.

Funcția UPPER

O funcție similară, returnează numai toate caracterele cu majuscule, acesta este SUPERIOR.

SELECȚI MAI MULTE (nume) CA FIO DIN tabel

nume - numele coloanei;
20 - numărul de caractere ( lungimea câmpului);
„-” este un caracter care trebuie completat la numărul necesar de caractere.

Funcția RPAD

Să luăm în considerare funcția inversă imediat. RPAD- acțiunea și sintaxa sunt aceleași ca și pentru LPAD, doar caracterele din dreapta sunt căptușite ( în LPAD din stânga).

SELECTAȚI RPAD (nume, 20, "-") CA nume DIN tabel

Ivan —————-

Serghei -----

Funcția LTRIM

Următorul este, de asemenea, o funcție utilă în unele cazuri, LTRIM- această funcție elimină caracterele din stânga pe care le specificați. De exemplu, aveți o coloană „oraș” în baza de date, în care orașul este indicat ca „Moscova” și există, de asemenea, orașe care sunt indicate doar ca „Moscova”. Dar trebuie să afișați raportul numai sub forma „Moscova” fără „g”. Trebuie doar să specificați un fel de tipar „g”. iar dacă caracterele din stânga încep cu „g”, atunci aceste caractere pur și simplu nu vor fi afișate.

SELECTAȚI LTRIM (oraș, „oraș”) CA gorod DIN tabel

Această funcție scanează caracterele din stânga, dacă nu există caractere în model la începutul șirului, atunci returnează valoarea inițială a celulei și, dacă există, le șterge.

Funcția RTRIM

Să aruncăm o privire la funcția inversă imediat. RTRIM- la fel ca LTRIM, numai căutările sunt căutate în partea dreaptă.

Notă! În Transact-SQL, funcțiile RTRIM și LTRIM elimină spațiile din dreapta și respectiv din stânga.

Funcția REPLACE

Acum să ne uităm la o funcție atât de interesantă ca A INLOCUI- returnează un șir în care toate potrivirile de caractere sunt înlocuite cu caracterele dvs. pe care le specificați. Pentru ceea ce poate fi folosit, de exemplu, aveți coloane în baza de date, în care există câteva simboluri de separare, de exemplu „/”. De exemplu, Ivan / Ivanov și doriți să-l afișați pe Ivan-Ivanov, apoi să scrieți

SELECTĂ ÎNLOCUIRE (nume, "/", "-") din tabel

și veți avea o înlocuire a simbolurilor.

Această funcție înlocuiește doar o potrivire completă de caractere, dacă de exemplu specificați „-” adică va căuta doar trei liniuțe și nu va înlocui fiecare liniuță individuală, spre deosebire de următoarea funcție.

Funcția TRADUCERE

TRADUCEȚI- o funcție de șir care înlocuiește toate caracterele din șir cu caracterele pe care le specificați. Pe baza numelui funcției, ați putea ghici că acesta este un flux de linie complet. Diferența dintre această funcție și REPLACE este că înlocuiește fiecare caracter pe care îl specificați, adică Aveți trei caractere, să spunem abc și cu ajutorul TRADUCERII îl puteți înlocui cu abc, astfel încât să aveți a = a, b = b, c = c și, conform acestui principiu, toate potrivirile de caractere vor fi înlocuite. Și dacă ați înlocuit cu REPLACE, atunci căutați doar o potrivire completă a simbolurilor ABC situate pe rând.

Funcția SUBSTR

SUBSTR- această funcție returnează numai gama de caractere pe care o specificați. Cu alte cuvinte, să spunem un șir de 10 caractere și nu aveți nevoie de toate cele zece, dar să presupunem că aveți nevoie doar de 3-8 ( de la a treia la a opta). Cu această funcție, puteți face acest lucru cu ușurință. De exemplu, aveți un identificator în baza de date cu o lungime fixă (cum ar fi AA-BB-55-66-CC) și fiecare combinație de caractere înseamnă ceva. Și într-un moment minunat ți s-a spus să afișezi doar 2 și 3 combinații de caractere, pentru aceasta scrii o interogare cu următoarea formă.

SELECT SUBSTR (ident, "4", "8") DIN tabel

acestea. imprimăm toate caracterele, începând cu 4 și terminând cu 8, iar după această interogare veți obține acest lucru:

Funcția LENGTH - lungimea șirului

Următoarea funcție poate fi, de asemenea, utilă LUNGIME- care numără doar numărul de caractere dintr-o linie. De exemplu, trebuie să aflați câte caractere sunt în fiecare celulă dintr-o coloană, să spunem „nume”, un tabel ca acesta.

SELECTAȚI LUNGIME (nume) DIN tabel

după această solicitare veți primi acest lucru.

Aici suntem cu dvs. și am examinat funcțiile de bază ale șirului SQL. Vom continua explorarea SQL în articolele viitoare.

Funcțiile Transact-SQL pot fi agregate sau scalare. Aceste tipuri de funcții sunt discutate în acest articol.

Funcții agregate

Funcțiile agregate efectuează calcule pe un grup de valori ale coloanei și returnează întotdeauna o singură valoare din aceste calcule. Transact-SQL acceptă mai multe funcții agregate comune:

AVG

Calculează media aritmetică a datelor dintr-o coloană. Valorile evaluate trebuie să fie numerice.

MIN și MAX

Determinați valoarea maximă și minimă a tuturor valorilor de date conținute în coloană. Valorile pot fi numerice, șiruri sau ora (data / ora).

SUMĂ

Calculează totalul valorilor dintr-o coloană. Valorile evaluate trebuie să fie numerice.

NUMARA

Numără numărul de valori care nu sunt nule într-o coloană. Funcția de numărare (*) este singura funcție agregată care nu efectuează calcule pe coloane. Această funcție returnează numărul de rânduri (indiferent dacă coloanele individuale conțin valori nule).

COUNT_BIG

Similar funcției de numărare, cu diferența că returnează o valoare de date BIGINT.

Utilizarea funcțiilor agregate obișnuite într-o instrucțiune SELECT va fi acoperită într-un articol viitor.

Funcții scalare

Funcțiile scalare Transact-SQL sunt utilizate în crearea expresiilor scalare. (O funcție scalară efectuează calcule pe o singură valoare sau o listă de valori, în timp ce o funcție agregată efectuează calcule pe un grup de valori din mai multe rânduri.) Funcțiile scalare pot fi clasificate după cum urmează:

funcții numerice;

funcții de dată;

funcții de șir;

funcțiile sistemului;

funcții de metadate.

Aceste tipuri de funcții sunt discutate în secțiunile următoare.

Funcții numerice

Funcțiile numerice Transact-SQL sunt funcții matematice pentru modificarea valorilor numerice. O listă a funcțiilor numerice și descrierea lor scurtă este dată în tabelul de mai jos:

Funcții numerice Transact-SQL

Funcţie	Sintaxă	Descriere	Exemplu de utilizare
ABS	ABS (n)	Returnează valoarea absolută (adică valorile negative sunt returnate ca pozitive) ale expresiei numerice n.	SELECT ABS (-5.320) - Returnează 5.320 SELECT ABS (8.90) - Returnează 8.90
ACOS, ASIN, ATAN, ATN2	ACOS (n), ASIN (n), ATAN (n), ATN2 (n, m)	Funcții trigonometrice inverse care calculează cosinusul invers, sinusul invers și arctangenta valorii lui n (pentru ATN2 se calculează arctangenta lui n / m). Valorile originale n, m și rezultatul sunt de tipul de date FLOAT.
COS, SIN, TAN, COT	COS (n), SIN (n), TAN (n), COT (n)	Funcții trigonometrice care calculează cosinusul, sinusul, tangenta, cotangenta valorii lui n. Rezultatul este de tipul de date FLOAT.
GRADE, RADIANI	GRADE (n), RADIANI (n)	Funcția DEGREES convertește radianii în grade, RADIANS invers.	SELECT DEGREES (PI () / 4) - Returnează 45 SELECT COS (RADIANS (60.0)) - Returnează 0,5
TAVAN	PLAFON (n)	Rotunjește un număr până la o valoare întreagă mai mare.	SELECT CEILING (-5.320) - Returnează -5 SELECT CEILING (8.90) - Returnează 9
RUNDĂ	RUND (n, p, [t])	Rotunjește n până la cel mai apropiat p. Când p este un număr pozitiv, partea fracționată a lui n este rotunjită, iar când este negativă, partea întreagă este rotunjită. Când utilizați argumentul opțional t, numărul n nu este rotunjit, ci trunchiat (adică rotunjit în jos).	SELECT ROUND (5.3208, 3) - Returnează 5.3210 SELECT ROUND (125.384, -1) - Returnează 130.000 SELECT ROUND (125.384, -1, 1) - Returnează 120.000
PODEA	ETAJ (n)	Se rotunjește la cea mai mică valoare întreagă.	SELECTARE ETAJ (5.88) - Returnează 5
EXP	EXP (n)	Calculează valoarea lui e n.
LOG, LOG10	LOG (n), LOG10 (n)	LOG (n) - calculează logaritmul natural (adică baza e) a lui n, LOG10 (n) - calculează logaritmul zecimal (baza 10) al lui n.
PI	PI ()	Returnează π (3,1415)
PUTERE	PUTERE (x, y)	Calculează valoarea x y.
RAND	RAND ()	Returnează un număr arbitrar de tip FLOAT în intervalul de valori cuprinse între 0 și 1.
ROWCOUNT_BIG	ROWCOUNT_BIG ()	Returnează numărul de rânduri de tabel care au fost procesate de ultima instrucțiune Transact-SQL executată de sistem. Valoarea returnată este de tip BIGINT.
SEMN	SEMN (n)	Returnează semnul valorii n ca număr: +1 dacă este pozitiv, -1 dacă este negativ.
SQRT, SQUARE	SQRT (n), SQUARE (n)	SQRT (n) - calculează rădăcina pătrată a lui n, SQUARE (n) - returnează pătratul argumentului n.

Funcții de dată

Funcțiile date evaluează părțile corespunzătoare ale datei sau orei unei expresii sau returnează o valoare a intervalului de timp. Funcțiile date acceptate de Transact-SQL și scurtele lor descrieri sunt prezentate în tabelul de mai jos:

Funcții Transact-SQL Date

Funcţie	Sintaxă	Descriere	Exemplu de utilizare
GETDATE	GETDATE ()	Returnează data și ora curente ale sistemului.	SELECTAȚI GETDATE ()
DATEPART	DATEPART (articol, dată)	Returnează partea de dată specificată în parametrul articolului ca număr întreg.	Returnează 1 (ianuarie) SELECT DATEPART (lună, "01/01/2012") - Returnează 4 (miercuri) SELECT DATEPART (ziua săptămânii, "01/02/2012")
DATENAME	DATENAME (articol, dată)	Returnează partea de dată specificată în parametrul articolului ca șir de caractere.	Returnează ianuarie SELECT DATENAME (luna, "01/01/2012") - Revine miercuri SELECT DATENAME (ziua săptămânii, "01/02/2012")
DATEDIFF	DATEDIFF (item, dat1, dat2)	Calculează diferența dintre cele două părți ale datelor dat1 și dat2 și returnează un rezultat întreg în unitățile specificate în argumentul articolului.	Returnează 19 (interval de 19 ani între date) SELECT DATEDIFF (an, "01/01/1990", "01/01/2010") - Returnează 7305 (7305 zile interval între date) SELECT DATEDIFF (zi, "01/01 / 1990 "," 01/01 .2010 ")
DATEADD	DATEADD (articol, n, dată)	Adăugă al doilea număr de unități specificat în argumentul articolului la data specificată. (Valoarea pentru n poate fi, de asemenea, negativă.)	Adăugați 3 zile la data curentă SELECT DATEADD (zi, 3, GETDATE ())

Funcții de șir

Funcțiile șir manipulează valorile coloanelor, care sunt de obicei de tipul datelor de caractere. Funcțiile șirului acceptate în Transact-SQL și scurta lor descriere sunt prezentate în tabelul de mai jos:

Funcțiile șirului Transact-SQL

Funcţie	Sintaxă	Descriere	Exemplu de utilizare
ASCII, UNICOD	ASCII (char), UNICODE (char)	Convertește caracterul specificat în numărul ASCII corespunzător.	SELECT ASCII ("W") - 87 SELECT UNICOD ("u") - 1102
CHAR, NCHAR	CHAR (int), NCHAR (int)	Convertește codul ASCII (sau Unicode dacă NCHAR) la caracterul corespunzător.	SELECT CHAR (87) - "W" SELECT NCHAR (1102) - "u"
CHARINDEX	CHARINDEX (str1, str2)	Returnează poziția inițială a apariției sub șirului str1 în șirul str2. Dacă șirul str2 nu conține sub șir str1, valoarea returnată este 0	Returnează 5 SELECTARE CHARINDEX („morf”, „polimorfism”)
DIFERENȚĂ	DIFERENȚĂ (str1, str2)	Returnează un număr întreg de la 0 la 4, care este diferența dintre valorile SOUNDEX ale celor două șiruri str1 și str2. Metoda SOUNDEX returnează un număr care caracterizează sunetul șirului. Folosind această metodă, puteți identifica șiruri similare. Funcționează numai pentru caractere ASCII.	Returnează 2 SELECT DIFERENȚĂ („ortografie”, „spionare”)
STANGA DREAPTA	STÂNGA (str, lungime), DREAPTA (str, lungime)	Returnează numărul primelor caractere ale șirului de caractere specificat de parametrul de lungime al LEFT și ultimele caractere de lungime ale șirului de caractere pentru funcția DREAPTA.	DECLARE @str nvarchar (30) = "Sincronizare"; - Returnează „Synx” SELECT STÂNGA (@str, 4) - Returnează „zats” SELECT DREAPTA (@str, 5)
LEN	LEN (str)	Returnează numărul de caractere (nu numărul de octeți) în str specificat în argument, inclusiv spațiile finale.
MAI MIC, SUPERIOR	LOWER (str), SUPERIOR (str)	Funcția LOWER convertește toate literele majuscule ale str1 în minuscule. Literele mici și alte caractere incluse în șir nu sunt afectate. Funcția UPPER convertește toate literele minuscule ale str în majuscule.	DECLARE @str nvarchar (30) = "Sincronizare"; - Returnează „SINCRONIZARE” SELECT SUPERIOR (@str) - Returnează „sincronizare” SELECT INFERIOR (@str)
LTRIM, RTRIM	LTRIM (str), RTRIM (str)	Funcția LTRIM elimină spațiile de conducere din șirul de caractere, RTRIM elimină spațiile de la sfârșitul șirului în consecință.
NUME CITAT	QUOTENAME (char_string)	Returnează un șir Unicode delimitat pentru a converti șirul de intrare într-un identificator delimitat valid.	DECLARE @str nvarchar (30) = "Sincronizare"; - Returnează „[Sincronizare” ”SELECȚIONEAZĂ NOMUL DE CITARE (@str)
PATINDEX	PATINDEX (% p%, expr)	Returnează poziția inițială a primei apariții a modelului p în expresia expr dată sau zero dacă modelul dat nu este găsit.	Returnează 4 SELECTARE PATINDEX ("% chro%", "Sincronizare")
A INLOCUI	ÎNLOCUIȚI (str1, str2, str3)	Înlocuiește toate aparițiile str2 din str1 cu str3.	Returnează „Desincronizare” SELECTARE ÎNLOCUIRE („Sincronizare”, „Sincronizare”, „Desincronizare”)
REPLICAȚI	REPLICAȚI (str, i)	Repetă șirul str i de ori.	Returnează „aBaBaBaBaB” SELECTARE REPLICARE („aB”, 5)
VERSO	INVERSA (str)	Ieșire șir str în ordine inversă.	Returnează „yicazinorkhnis” SELECTARE INVERSARE („Sincronizare”)
SOUNDEX	SOUNDEX (str)	Returnează sunetul cu patru caractere utilizat pentru a determina similaritatea a două șiruri. Funcționează numai pentru caractere ASCII.
SPAŢIU	SPACE (lungime)	Returnează un șir de spații cu lungimea specificată în parametrul lungime. Analog cu REPLICATE ("", lungime).
STR	STR (f [, len [, d]])	Convertește expresia în virgulă mobilă specificată f într-un șir, unde len este lungimea șirului, incluzând virgula, semnul, numerele și spațiile (implicit este 10) și d este numărul de zecimale de returnat.	Returnează "3.14" SELECT STR (3.1415, 4, 2)
CHESTIE	STUFF (str1, a, lungime, str2)	Elimină caracterele de lungime din șirul str1, începând de la poziția a și introduce șirul str2 în locul lor.	Notă într-o carte SELECT STUFF ("Notebook", 5, 0, "in a") - Manual SELECT STUFF ("Notebook", 1, 4, "Hand")
SUBSTRING	SUBSTRING (str1, a, lungime)	Preluează din șir str, începând de la poziția a, un șir de lungime lungime.

Funcțiile sistemului

Funcțiile sistemului Transact-SQL oferă informații extinse despre obiectele bazei de date. Majoritatea funcțiilor de sistem utilizează un identificator numeric intern (ID) care este atribuit fiecărui obiect de bază de date atunci când este creat. Prin acest identificator, sistemul poate identifica în mod unic fiecare obiect din baza de date.

Următorul tabel listează unele dintre cele mai importante funcții ale sistemului împreună cu o scurtă descriere:

Funcțiile sistemului Transact-SQL

Funcţie	Sintaxă	Descriere	Exemplu de utilizare
CAST	CAST (w tip AS [[lungime]]	Convertește expresia w la tipul de date specificat, dacă este cazul. Argumentul w poate fi orice expresie validă.	Va returna 3 SELECT CAST (3.1258 AS INT)
COALESCE	COALESCE (a1, a2)	Returnează prima valoare de expresie non-nulă din lista de expresii a1, a2, ....
COL_LENGTH	COL_LENGTH (obj, col)	Returnează lungimea coloanei col a obiectului bazei de date (tabel sau vizualizare) obj.	Returnează 4 SELECT COL_LENGTH („Angajat”, „Id”)
CONVERTIT	CONVERT (tip [[lungime]], w)	Echivalent cu funcția CAST, dar argumentele sunt specificate diferit. Poate fi utilizat cu orice tip de date.
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Returnează data și ora curente.
UTILIZATOR CURENT	UTILIZATOR CURENT	Returnează numele utilizatorului curent.
DATALENGTH	DATALENGTH (z)	Returnează numărul de octeți pe care îl ocupă expresia z.	Această interogare returnează lungimea fiecărui câmp SELECT DATALENGTH (FirstName) FROM Employee
GETANSINULL	GETANSINULL ("dbname")	Returnează 1 dacă utilizarea valorilor nule în baza de date dbname îndeplinește standardul ANSI SQL.
ISNULL	ISNULL (expr, valoare)	Returnează valoarea expr dacă nu este NULL; în caz contrar, valoarea este returnată.
ISNUMERIC	ISNUMERIC (expr)	Determină dacă expr este un tip numeric valid.
NEWID	NEWID ()	Creează un șir binar unic de 16 octeți pentru stocarea valorilor tipului de date UNIQUEIDENTIFIER.
NEWSEQUENTIALID	NEWSEQUENTIALID ()	Creează un GUID care este mai mare decât orice alt GUID generat anterior de această funcție pe computerul specificat. (Această funcție poate fi utilizată doar ca implicită pentru o coloană.)
NULLIF	NULLIF (expr1, expr2)	Returnează nul dacă valorile expr1 și expr2 sunt aceleași.	Interogarea returnează NULL pentru proiect - pentru care Number = "p1" SELECT NULLIF (Number, "p1") FROM Project
PROPRIETATE SERVER	SERVERPROPERTY (nume de proprietate)	Obține informații despre proprietățile serverului de baze de date.
SYSTEM_USER	SYSTEM_USER	Returnează ID-ul utilizatorului curent.
NUMELE DE UTILIZATOR	NUMELE DE UTILIZATOR ()	Returnează numele de utilizator ID utilizator. Dacă nu este specificat niciun utilizator, atunci se returnează ID-ul curent.
NUME DE UTILIZATOR	NUME DE UTILIZATOR ()	Returnează numele de utilizator cu ID-ul specificat. Dacă nu este specificat niciun identificator, se returnează numele utilizatorului curent.

Funcții de metadate

Practic, funcțiile de metadate returnează informații despre o bază de date și obiecte de bază de date specificate. Tabelul de mai jos rezumă unele dintre cele mai importante funcții de metadate, împreună cu o scurtă descriere:

Funcții Metadate Transact-SQL

Funcţie	Sintaxă	Descriere	Exemplu de utilizare
COL_NAME	COL_NAME (tab_id, col_id)	Returnează numele coloanei cu col_id specificat al tabelului cu tab_id.	Returnează numele coloanei „LastName” SELECT COL_NAME (OBJECT_ID („Angajat”), 3)
PROPRIETATE DE COLONIE	COLUMNPROPERTY (id, col, proprietate)	Returnează informații despre coloana specificată.	Returnează valoarea proprietății PRECISION - pentru coloana Id din tabelul Angajați SELECT COLUMNPROPERTY (OBJECT_ID ("Angajat"), "Id", "precizie")
PROPRIETATE DE BAZĂ DE DATE	DATABASEPROPERTY (bază de date, proprietate)	Returnează valoarea proprietății proprietății bazei de date.	Returnează valoarea proprietății IsNullConcat - pentru baza de date SampleDb SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat")
DB_ID	DB_ID ()	Returnează identificatorul bazei de date db_name. Dacă nu este specificat nici un nume de bază de date, atunci se returnează identificatorul curent al bazei de date.
DB_NAME	DB_NAME ()	Returnează numele bazei de date cu db_id. Dacă nu este specificat niciun identificator, atunci se returnează numele bazei de date curente.
INDEX_COL	INDEX_COL (tabel, i, nu)	Returnează numele coloanei indexate a tabelului tabelului. Coloana este indicată de identificatorul de index i și de poziția nr a coloanei din acel index.
PROPRIETATE INDEX	INDEXPROPERTY (obj_id, index_name, property)	Returnează proprietățile indexului sau statisticilor denumite pentru numărul de identificare al tabelului specificat, numele indexului sau statisticilor și numele proprietății.
OBJECT_NAME	OBJECT_NAME (obj_id)	Returnează numele obiectului bazei de date cu identificatorul obj_id.	SELECT OBJECT_NAME (245575913);
ID_OBIECT	OBJECT_ID (obj_name)	Returnează identificatorul de obiect obiect_name al bazei de date.	Returnează 245575913 - ID tabel angajat SELECT OBJECT_ID („Angajat”)
PROPRIETATE DE OBIECT	OBJECTPROPERTY (obj_id, proprietate)	Returnează informații despre obiecte din baza de date curentă.

Buna dragi cititori ai site-ului blogului. Astăzi aș dori să vorbesc despre limbajul SQL și, în special, despre funcțiile de procesare a textului. Pentru a crea și gestiona un site web, de multe ori nu este necesar să cunoașteți limbajul SQL. Sistemele de gestionare a conținutului vă permit să editați conținutul site-ului fără a scrie interogări. Dar cel puțin o cunoaștere superficială a limbajului de interogare structurat vă va ajuta să accelerați semnificativ modificarea și gestionarea datelor din baza de date a site-ului dvs.

De multe ori mă confrunt cu sarcini: să elimin o parte din text din câmpurile de text ale bazei de date, să combin date de șir sau altceva legat de text. A face toate acestea prin intermediul panourilor de administrare a site-ului este foarte incomod și sumbru. Este mult mai ușor să scrieți o interogare în baza de date care face toate aceste lucruri în câteva secunde.

Deci, să începem ...

Funcții simbolice în limbaj sql

Să începem în ordine cu cele mai simple. Mai întâi, să ne uităm la funcția șir ASCII, care este utilizată pentru a determina codul ASCII al caracterelor text:

întreg ASCII(str şir)

Funcția returnează o valoare întreagă - codul ASCII al primului caracter stâng al șirului str. Dacă șirul str este gol, returnează 0 și NULL dacă șirul str nu există.

SELECT ASCII ("t");
Poziție: 116
SELECT ASCII ("test");
Poziție: 116
SELECȚIONEAZĂ ASCII (1);
Poziție: 49

întreg ORD(str şir)

Dacă primul caracter stâng al șirului str este multibyte, acesta își returnează codul în formatul: ((cod ASCII primul octet) * 256 + (cod ASCII al doilea octet)) [* Cod ASCII al treilea octet ...]. Dacă primul caracter stâng al șirului str nu este multibyte, acesta funcționează ca o funcție ASCII - returnează codul ASCII.

SELECT ORD ("test");
Poziție: 116

Funcția CHAR, care este strâns legată de funcția ASCII, face opusul:

şir CHAR(int întreg, ...)

Funcția CHAR returnează un șir de caractere bazat pe codurile lor ASCII. Dacă NULL este găsit printre valori, acesta este omis.

SELECT CHAR (116, "101", 115, "116");
Rezultat: „test”

Funcții SQL pentru concatenarea șirurilor

Una dintre cele mai populare categorii de caracteristici. La urma urmei, este adesea necesar să combinați valorile câtorva câmpuri ale tabelelor bazei de date a site-ului. Există mai multe funcții în limbajul SQL simultan concatenare șir.

Funcția CONCAT:

şir CONCAT(str1 şir, str2 şir,...)

Funcția returnează un șir creat prin concatenarea argumentelor. Pot fi specificate mai mult de două argumente. Dacă unul dintre argumente este NULL, atunci rezultatul returnat va fi NULL. Valorile numerice sunt convertite în șiruri.

SELECTAȚI CONCAT ("Bună ziua", "", "lume", "!");
Rezultat: "Bună ziua lume!"
SELECT CONCAT ("Bună ziua", NULL, "lume", "!");
Rezultat: NUL
SELECT CONCAT ("Number pi", "=", 3.14);
Rezultat: „Număr pi = 3,14”

După cum puteți vedea din exemple, șirurile sunt concatenate fără delimitatori. Pentru a separa cuvintele din primul exemplu, trebuie să utilizați un spațiu ca argument. Dacă ar exista mai multe cuvinte, atunci nu ar fi foarte convenabil să introduceți spații de fiecare dată.

Pentru astfel de cazuri, există funcția CONCAT_WS:

şir CONCAT_WS(separator şir, str1 şir, str2 şir,...)

Funcția concatenează șiruri precum funcția CONCAT, dar inserează un separator între argumente. Dacă separatorul este NULL, atunci rezultatul este NULL. Argumentele șir NULL sunt omise.

SELECT CONCAT_WS ("", "Ivanov", "Ivan", "Ivanovich");
Rezultat: „Ivanov Ivan Ivanovici”
SELECTAȚI CONCAT_WS (NULL, "Ivanov", "Ivan", "Ivanovich");
Rezultat: NUL
SELECTAȚI CONCAT_WS ("", "Ivanov", NULL, "Ivan", "Ivanovich");
Rezultat: "" Ivanov Ivan Ivanovici "

În cazul concatenării unui număr mare de șiruri care trebuie separate de un separator, funcția CONCAT_WS este mult mai convenabilă decât funcția CONCAT.

Uneori este necesar să prelungiți un șir la un anumit număr de caractere prin repetarea unui caracter. Acesta este și un fel de concatenare de șiruri. Pentru a face acest lucru, puteți utiliza funcțiile LPADși RPAD... Funcțiile au următoarea sintaxă:

şir LPAD(str şir, len întreg, padstr şir)
şir RPAD(str şir, len întreg, padstr şir)

Funcția LPAD returnează str, căptușit la stânga cu padstr la lungimea lungimii. Funcția RPAD face același lucru, cu excepția faptului că alungirea are loc pe partea dreaptă.

SELECT LPAD ("test", 10, ".");
Rezultat: ...... test
SELECT RPAD ("test", 10, ".");
Rezultat: test ......

În aceste funcții, trebuie să fiți atenți parametru len, care limitează numărul de caractere care trebuie afișate. Prin urmare, dacă lungimea șirului str este mai mare decât parametrul len, atunci șirul va fi trunchiat:

SELECT LPAD ("test", 3, ".");
Rezultat: tes

Determinarea lungimii șirului în interogările sql

Pentru a determina numărul de caractere dintr-un șir în limbajul SQL, funcția LENGTH este responsabilă - lungimea șirului:

întreg LUNGIME(str şir)

Funcția returnează un număr întreg egal cu numărul de caractere din str.

SELECȚIE LUNGIME („test”);
Rezultat: 4

În cazul utilizării codificărilor multibyte, funcția LENGTH nu returnează rezultatul corect. De exemplu, dacă codificarea unicode este setată, atunci cererea:

SELECȚIE LUNGIME („test”);

va reveni 8. Care, puteți vedea cu ușurință, este de două ori numărul real de caractere. În acest caz, trebuie să utilizați funcția CHAR_LENGTH:

întreg CHAR_LENGTH(str şir)

Funcția revine și ea lungimea liniei str și suporturi caractere multibyte.

De exemplu:

SELECT CHAR_LENGTH ("test");
Rezultat: 4

Căutați un șir într-un șir folosind sql

Există mai multe funcții pentru calcularea poziției unui șir într-un șir în limba sql. Primul pe care îl vom analiza este funcția POZIȚIE:

întregPOZIŢIE(substr şir IN str şir)

Returnează numărul poziției primei apariții a substringului substring în șirul str și returnează 0 dacă substringul nu este găsit. Funcția POZIȚIE poate gestiona caractere multibyte.

SELECȚIAȚI POZIȚIA ("cd" ÎN "abcdcde");
Rezultat: 3
SELECT POZIȚIE („xy” ÎN „abcdcde”);
Rezultat: 0

Următoarea funcție LOCATE vă permite să începeți să căutați un șir sub o anumită poziție:

întreg LOCALIZA(substr şir, str şir, poz întreg)

Returnează poziția primei apariții a substringului substring în șir str, începând de la poziția pos. Dacă parametrul pos nu este specificat, atunci căutarea se efectuează de la începutul șirului. Dacă substratul nu este găsit, acesta returnează 0. Acceptă caractere multibyte.

SELECT LOCATE ("cd", "abcdcdde", 5);
Rezultat: 5
SELECT LOCATE ("cd", "abcdcdde");
Rezultat: 3

Funcția INSTR este analogă funcțiilor POZIȚIE și LOCALIZARE:

întreg INSTR(str şir, substr şir)

Returnează poziția primei apariții a substringului substring în șirul str, precum și a funcției de mai sus. Singura diferență față de funcțiile POSITION și LOCATE este că argumentele sunt schimbate.

În primul rând, vom lua în considerare două funcții STÂNGA și DREPT simultan, care sunt similare în acțiunea lor:

şir STÂNGA(str şir, len întreg)
şir DREAPTA(str şir, len întreg)

Funcția STÂNGA returnează len-ul primelor caractere din șir str, iar funcția DREAPTA returnează același număr dintre ultimele. Suportă caractere multibyte.

SELECT STÂNGA („Moscova”, 3);
Rezultat: Mos
SELECT DREAPTA ("Moscova", 3);
Rezultat: kva

şir SUBSTRING(str şir, poz întreg, len întreg)
şir MID(str şir, poz întreg, len întreg)

Funcțiile vă permit să obțineți un șir de caractere de lungime str din poziția poz. Dacă parametrul len nu este specificat, atunci se restituie întregul șir care începe de la poziția pos.

SELECT SUBSTRING („Moscova este capitala Rusiei”, 4, 6);
Rezultat: Moscova
SELECT SUBSTRING („Moscova este capitala Rusiei”, 4);
Rezultat: Moscova este capitala Rusiei

Nu dau exemple cu funcția MID, deoarece rezultatele vor fi similare.

Interesantă funcție SUBSTRING_INDEX:

şir SUBSTRING_INDEX(str şir, delim şir, numara întreg)

Funcția returnează un subșir de str obținut prin eliminarea caracterelor după delimitarea la numărul de poziții. Parametrul de numărare poate fi fie pozitiv, fie negativ. Dacă numărul este pozitiv, atunci poziția separatorului va fi numărată din stânga și caracterele din dreapta separatorului vor fi șterse. Dacă numărul este negativ, atunci poziția separatorului este numărată la dreapta și caracterele din stânga separatorului sunt eliminate. Poate că descrierea sa dovedit a fi prea confuză, dar cu exemple va deveni mai clară.

SELECT SUBSTRING_INDEX ("www.mysql.ru", ".", 1);
Rezultat: www

În acest exemplu, funcția găsește prima apariție a caracterului punct în șirul „www.mysql.ru” și elimină toate caracterele care îl urmează, inclusiv separatorul în sine.

SELECT SUBSTRING_INDEX ("www.mysql.ru", ".", 2);
Rezultat: www.mysql

Aici, funcția caută a doua apariție a punctului, elimină toate caracterele din dreapta acestuia și returnează șirul rezultat. Și încă un exemplu cu o valoare negativă pentru parametrul count:

SELECT SUBSTRING_INDEX ("www.mysql.ru", ".", -2);
Rezultat: mysql.ru

În acest exemplu, funcția SUBSTRING_INDEX caută cel de-al doilea punct numărând din dreapta, elimină caracterele din stânga acestuia și scoate subșirul rezultat.

Eliminarea spațiilor dintr-un șir

Există trei funcții în SQL pentru a elimina spații suplimentare de la începutul și sfârșitul unui șir.

Funcția LTRIM:

şir LTRIM(str şir)

Elimină spațiile de la începutul str și returnează rezultatul.

Funcția RTRIM:

şir RTRIM(str şir)

De asemenea, elimină spațiile din str, doar de la sfârșit. Ambele funcții acceptă caractere multibyte.

SELECT LTRIM („text”);
Rezultat: „text”
SELECT RTRIM („text”);
Rezultat: „text”

Iar a treia funcție TRIM vă permite să eliminați imediat spațiile de la începutul și de la sfârșitul unui șir:

şir TUNDE([ şir FROM] str şir)

Parametrul str este necesar, restul parametrilor sunt opționali. Dacă este specificat un singur parametru str, atunci returnează șirul str eliminând spațiile de la începutul și sfârșitul șirului în același timp.

SELECT TRIM ("text");
Rezultat: „text”

Parametrul remstr poate fi utilizat pentru a specifica caractere sau șiruri de caractere care trebuie eliminate de la începutul și sfârșitul șirului. Folosind parametrii de control AMB, LEADING, TRAILING, puteți specifica de unde vor fi eliminate caracterele:

AMANDOA - elimina substringul remstr de la inceput si de la sfarsitul sirului;
LEADING - elimină remstr de la începutul liniei;
TRAILING - elimină remstr de la capătul liniei.

SELECȚIONEAZĂ TRIM (AMBE "și" DIN "text");
Rezultat: „text”
SELECT TRIM (LEADING "și" FROM "textaa");
Rezultat: "textaa"
SELECT TRIM (TRAILING "și" FROM "aaatext");
Rezultat: „aaatext”

Funcția SPACE vă permite să obțineți un șir format dintr-un anumit număr de spații:

şir SPAŢIU(n întreg)

Returnează un șir format din n spații.

Funcția REPLACE este necesară pentru înlocuiește caracterele date într-un șir:

şir A INLOCUI(str şir, din_str şir, to_str şir)

Funcția înlocuiește toate șirurile de la_str cu to_str în str și returnează rezultatul. Suportă caractere multibyte.

SELECȚIONEAZĂ ÎNLOCUIREA („înlocuirea șirului”, „șirul”, „text”)
Rezultat: „înlocuire text”

Funcția REPEAT:

şir REPETA(str şir, numara întreg)

Funcția returnează un șir care constă în număr de repetări ale str. Suportă caractere multibyte.

SELECTAȚI REPETAREA ("w", 3);
Rezultat: „www”

Funcția INVERSARE inversează linia:

şir VERSO(str şir)

Schimbă toate caracterele de la ultimul la primul din str și returnează rezultatul. Suportă caractere multibyte.

SELECȚIA INVERSA („text”);
Rezultat: "tsket"

Funcția INSERT pentru a insera un șir de caractere într-un șir:

şirINTRODUCE(str şir, poz întreg, len întreg, newstr şir)

Returnează șirul rezultat din inserarea substringului newstr în str în poziția poz. Parametrul len specifică câte caractere vor fi eliminate din str, începând cu poziția poz. Suportă caractere multibyte.

SELECT INSERT („text”, 2, 5, „MySQL”);
Rezultat: „tMySQL”
"SELECT INSERT (" text ", 2, 0," MySQL ");
Rezultat: „tMySQLext”
SELECT INSERT („introduceți text”, 2, 7, „MySQL”);
Rezultat: "SELECT INSERT (" introduceți text ", 2, 7," MySQL ");"

Dacă dintr-o dată trebuie să înlocuiți toate literele majuscule din text cu litere mari, atunci puteți utiliza una dintre cele două funcții:

şir CLASE(str şir) și şir INFERIOR(str şir)

Ambele funcții înlocuiesc literele majuscule în str și returnează rezultatul. Ambele acceptă caractere multibyte.

SELCET LOWER ("ABVGDeZhZiKL");
Rezultat: "abvgdezhzikl"

Dacă, dimpotrivă, este necesar să se înlocuiască majusculele cu majuscule, atunci una dintre cele două funcții poate fi folosită și:

şir UCASE(str şir) și şir SUPERIOR (str şir)

Funcțiile returnează șirul de caractere, înlocuind toate caracterele majuscule cu cele majuscule. Suportă și caractere multibyte.
Exemplu:

SELECȚI SUPERIOR ("Abvgdezhz");
Rezultat: „ABVGDEZHZ”

Există un pic mai multe funcții de șir în limbajul SQL decât cele discutate în acest articol. Dar, deoarece chiar și cele mai multe dintre funcțiile discutate aici sunt rareori folosite, voi finaliza revizuirea acestora. În articolele următoare, voi încerca să analizez exemple practice reale de utilizare a funcțiilor de șir SQL. Așadar, nu uitați să vă abonați la actualizările blogului. Pana data viitoare!

Iată o listă completă a funcțiilor șirului preluate din BOL:

Rezultat - 11. Pentru a afla ce litere sunt, putem folosi funcția CHAR, care returnează un caracter folosind un cod ASCII cunoscut (de la 0 la 255):

Iată cum, de exemplu, puteți obține un tabel de coduri cu toate caracterele alfabetice:

SELECTARE CHAR (ASCII ("a") + num-1) literă, ASCII ("a") + num - 1 FROM (SELECT 5 * 5 * (a-1) + 5 * (b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y CROSS JOIN ( SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z) x WHERE ASCII ("a") + num -1 BETWEEN ASCII ("a") AND ASCII ("z")

Pentru cei care nu sunt încă conștienți de generarea unei secvențe de numere, mă refer la articolul corespunzător.

După cum știți, codurile pentru literele mici și majuscule sunt diferite. Prin urmare, pentru a obține un set complet fără a rescrie interogarea, trebuie doar să adăugați unul similar codului de mai sus:

Presupun că nu va fi dificil să adăugați această scrisoare la masă, dacă este necesar.

Luați în considerare acum problema găsirii subșirului necesar într-o expresie șir. Există două funcții care pot fi utilizate pentru aceasta - CHARINDEXși PATINDEX... Ambii returnează poziția de pornire (poziția primului caracter al șirului) al șirului din șir. Funcția CHARINDEX are sintaxa:

CHARINDEX ( expresie_consultare, string_expression[, start_position])

Iată un parametru întreg opțional start_position definește poziția în expresia șir din care se efectuează căutarea expresie_cercetare... Dacă acest parametru este omis, căutarea se efectuează de la început string_expression... De exemplu, cererea

Trebuie remarcat faptul că, dacă substringul dorit sau expresia șirului este NULL, atunci rezultatul funcției va fi și NULL.

Următorul exemplu definește pozițiile primei și celei de-a doua apariții a caracterului „a” în numele navei „California”

Și aici, de exemplu, cum puteți găsi numele navelor care conțin o succesiune de trei caractere, dintre care primul și ultimul este „e”:

Baie de aburi STÂNGA funcţie DREAPTA returnează numărul specificat de caractere la dreapta unei expresii șir:

DREAPTA (<expresia șirului>,<numărul de caractere>)

De exemplu, iată cum puteți identifica numele navei care încep și se termină cu aceeași literă:

Aici separăm numele clasei și numele navei cu un spațiu. În plus, pentru a nu repeta întreaga construcție ca argument pentru o funcție, folosim o subinterogare. Rezultatul va arăta astfel:

Pentru a elimina acest caz, puteți utiliza o altă funcție utilă LEN (<expresia șirului>) care returnează numărul de caractere din șir. Să ne limităm la cazul în care numărul de caractere este mai mult de unul:

Funcţie REPLICAȚI umplerea constantei "abcde" cu cinci spații la dreapta, care nu sunt luate în considerare de funcție LEN, - în ambele cazuri obținem 5.
Funcţie DATALENGTH returnează numărul de octeți în reprezentarea unei variabile și ne arată diferența dintre CHAR și VARCHAR. DATALENGTH ne va da 12 pentru CHAR și 10 pentru VARCHAR.
Cum era de așteptat, DATALENGTH a returnat lungimea reală a variabilei pentru o variabilă VARCHAR. Dar de ce rezultatul este egal cu 12 pentru o variabilă de tip CHAR? Ideea este că CHAR este un tip lungime fixă... Dacă valoarea variabilei este mai mică decât lungimea ei și am declarat lungimea ca CHAR (12), atunci valoarea variabilei va fi „aliniată” la lungimea necesară prin adăugarea de spații.

Există sarcini pe site în care doriți să comandați (găsiți maximul etc.) în ordine numerică valorile prezentate în format text. De exemplu, numărul scaunului avionului („2d”) sau viteza CD-ului („24x”). Problema este că textul este sortat astfel (crescător)

Dacă doriți să aranjați locurile în ordine crescătoare de rânduri, atunci ordinea ar trebui să fie după cum urmează

Dacă ne limităm doar la asta, atunci obținem

Rămâne să sortăm

Astăzi propun să luăm în considerare exemple simple de utilizare Funcțiile șirului Transact-SQL, și nu doar o descriere și exemple ale unor funcții, ci combinația lor, adică cum puteți să le cuibăriți unul în celălalt, deoarece funcțiile standard nu sunt suficiente pentru a implementa multe sarcini și trebuie să le folosiți împreună. Așadar, aș dori să vă arăt câteva exemple simple de scriere a acestor interogări.

Ne-am uitat deja la funcțiile șirului SQL, dar din moment ce implementările acestui limbaj în diferite SGBD sunt diferite, de exemplu, unele funcții nu sunt în Transact-SQL, dar în PL / PGSql sunt și doar ultima dată am analizat șirul funcții care pot fi utilizate în plpgsql și, prin urmare, astăzi vom vorbi în mod specific despre Transact-SQL.

Cum pot fi combinate SUBSTRING, CHARINDEX și LEN

Și astfel, de exemplu, trebuie să căutați o parte din ea într-un șir conform unui anumit criteriu și să o decupați și nu doar să căutați o parte de același tip, ci dinamic, adică șirul de căutare va fi diferit pentru fiecare linie. Exemple vor fi scrise în Management Studio SQL Server 2008.

Pentru aceasta vom folosi următoarele funcții:

SUBSTRING(str, start, len) - această funcție decupează o parte dintr-un șir dintr-un alt șir. Are trei parametri 1. Acesta este șirul în sine; 2. Poziția inițială din care să înceapă tăierea; 3. Numărul de caractere, câte trebuie tăiate.
CHARINDEX(str1, str2) - caută str1 în str2 și returnează ordinalul primului caracter dacă se găsește un astfel de șir. Are un al treilea parametru opțional, cu care puteți specifica din ce parte începeți căutarea.
LEN(str1) este lungimea șirului, adică Personaje.

După cum puteți vedea, aici am folosit declarația de variabile și puteți înlocui propriile câmpuri în cerere în loc de variabile. Iată codul în sine:

Declare @rezult ca varchar (10) - șir sursă declare @ str1 ca varchar (100) - căutare șir declara @ str2 ca varchar (10) set @ str1 = "Testați șirul șirului pentru a căuta un alt șir în el" set @ str2 = "șir" set @ rezult = substring (@ str1, CHARINDEX (@ str2, @ str1), LEN (@ str2)) selectați @rezult

Ideea aici este că, folosind funcția len, aflăm câte caractere trebuie tăiate, iar charindex stabilește poziția din care să înceapă tăierea și, în consecință, șirul efectuează selecția în sine în șir.

Cum se pot combina STÂNGA, DREAPTA și LEN

Să presupunem că trebuie să obțineți primele caractere dintr-un șir sau să verificați primele caractere dintr-un șir pentru prezența a ceva, de exemplu, un anumit număr, iar lungimea acestuia este natural diferită (un exemplu de test, desigur).

Stânga(str, kol) - funcția elimină din stânga numărul specificat de caractere, are doi parametri, primul este un șir și, respectiv, al doilea, numărul de caractere;
Dreapta(str, kol) - funcția elimină din dreapta numărul specificat de caractere, parametrii sunt aceiași.

Acum vom folosi interogări simple împotriva tabelului

În primul rând, să creăm un test_table:

CREATE TABLE (IDENTITY (1,1) NOT NULL, (18, 0) NULL, (50) NULL, CONSTRAINT KEY PRIMARY CLUSTERED (ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCPOWS = ON) ) ON GO

Să-l completăm cu date de testare și să scriem următoarele interogări:

După cum ați înțeles, prima interogare este doar o selecție a tuturor rândurilor (SQL Basics - select statement), iar a doua este o combinație directă a funcțiilor noastre, iată codul:

Selectați * din test_table selectați numărul, stânga (text, LEN (număr)) ca str din test_table

Și dacă aceste numere ar fi în dreapta, atunci am folosi funcția DREAPTA.

Folosind Rtrim, Ltrim, Upper și Lower în combinație

Presupunând că aveți o linie cu spații la început și la sfârșit și, bineînțeles, ați scăpa de ele și, de exemplu, le-ați face, astfel încât prima literă din această linie să fie capitalizată.

Rtrim(str) - elimină spațiile din dreapta;
Ltrim(str) - elimină spațiile din stânga;
Superior(str) - convertește șirul în litere mari;
Inferior(str) - convertește șirul în minuscule.

După cum puteți vedea, pentru remedierea am folosit și aici Substringși Len... Semnificația interogării este simplă, eliminăm spații atât din dreapta, cât și din stânga, apoi convertim primul caracter în majuscule tăindu-l, apoi concatenăm (operator +) acest caracter cu restul șirului. Iată codul:

Declarați @ str1 ca varchar (100) set @ str1 = "șir de testare cu spații principale și finale" selectați @ str1 selectați upper (substring (rtrim (ltrim (@ str1)), 1,1)) + lower (substring (rtrim ( ltrim (@ str1)), 2, LEN (rtrim (ltrim (@ str1))) - 1))

Pentru astăzi, cred că este suficient și, dacă vă place programarea în SQL, atunci pe acest site am atins acest subiect foarte interesant de mai multe ori, de exemplu.