SQLite Funkce pro práci s řetězci: REPLACE, SUBSTR, TRIM, LENGTH

Jak nastavit SQLite a spouštět dotazy na řetězcové funkce

SQLite je standardně vybaven seznamem vestavěných funkcí v rámci SQLite knihovna. Můžete také přidat některé další uživatelské funkce pomocí jazyka C, abyste rozšířili základní funkce.

Všimněte si, že pro všechny následující příklady je nutné spustit soubor sqlite3.exe a otevřít připojení k ukázkové databázi takto:

Krok 1) V tomto kroku otevřete složku Tento počítač, přejděte do adresáře „C:\sqlite“ a poté otevřete soubor „sqlite3.exe“:

Krok 2) Otevřete databázi „TutorialsSampleDB.db“ pomocí následujícího příkazu:

Nyní jste připraveni spustit jakýkoli dotaz a vyzkoušejte jakékoli dotazy použité v následujících částech.

Hledání LENGTH řetězce v SQLite

Chcete-li zjistit délku řetězce, použijte LENGTH(X), kde X je hodnota řetězce. Pokud je X nulová hodnota, funkce length vrátí nulovou hodnotu.

K získání délky číselné hodnoty můžete také použít funkci délky s číselnými hodnotami.

Příklad:

V následujícím příkladu vyzkoušíme syntaxi funkce LENGTH s následujícími hodnotami:

SELECT LENGTH('A string'), LENGTH(NULL), LENGTH(20), LENGTH(20.5);

To vám dá:

Výsledek je následující:

• LENGTH('Řetězec') vrátí 8, což je délka řetězce „A řetězec“.
• LENGTH(NULL) vrátí hodnotu null.
• LENGTH(20) vrátí 2, protože 20 je 2 délky.
• LENGTH(20.5) vrátí 4; Plovoucí desetinná čárka "." se počítá jako znak, takže budete mít čtyři znaky – 3 znaky, které mají délku čísel. Plus ještě jeden znak pro plovoucí desetinnou čárku „.“.

Výměna pouzdra s funkcí UPPER a LOWER

UPPER(X) vrátí stejný řetězec x, ale bude reprezentovat všechny znaky řetězce velkými znaky.

LOWER(X) vrátí stejný řetězec x, ale bude reprezentovat všechny znaky řetězce malými znaky.

UPPER a LOWER vrátí hodnoty null, pokud jim předáte hodnotu null.

Pokud předáte číselné hodnoty do UPPER nebo LOWER, obě vrátí přesnou číselnou hodnotu.

Příklad:

SELECT UPPER('a string'), LOWER('A STRING'), UPPER(20), LOWER(20), UPPER(NULL), LOWER(NULL);

To vám dá:

Výsledek je následující:

• UPPER('řetězec') vrací řetězec „řetězec“ ve formě velkých písmen „ŘETĚZEC“.
• LOWER('ŘETĚZEC') vrací řetězec „ŘETĚZEC“ malými písmeny v podobě „řetězec“.
• UPPER(20), LOWER(20) vrátí stejné číslo, protože nemají žádný vliv na čísla.
• UPPER(NULL), LOWER(NULL) vrátí hodnotu null, protože jsme jim předali hodnotu null.

SQLite Funkce SUBSTR

Funkce SUBSTR vrací zadaný počet řetězců, počínaje od určité pozice. Funkci můžete předat tři operandy, například takto „SUBSTR(X,Y,Z)“ takto:

• X je řetězcový literál nebo sloupec řetězce k analýze. Můžete předat doslovnou hodnotu (statickou hodnotu) nebo název sloupce a v tomto případě bude hodnota načtena z hodnot sloupce.
• Y je výchozí pozice pro extracřetězec začínající od něj.
• Z je počet znaků, které se mají analyzovat, počínaje konkrétní pozicí Y. Toto číslo je volitelné, můžete ho ignorovat, a v tomto případě SQLite bude bývalýtracŘetězec začínající od pozice Y určuje konec řetězce.

Příklad:

V následujícím dotazu použijeme funkci SUBSTR k získání 4 znaků počínaje druhým znakem ze jmen studentů:

SELECT StudentName, SUBSTR(StudentName, 2, 4), SUBSTR(StudentName, 2)
FROM Students;

To vám dá:

• SUBSTR(JménoStudenta, 2, 4) podřetězec „JménoStudenta“ počínaje druhým znakem a vrací další 4 znaky.
• Ve funkci SUBSTR(JménoStudenta, 2) jsme však nezadali počet znaků, které se mají vrátit, proto SQLite vrátil všechny zbývající znaky v poli „StudentName“ počínaje druhou pozici.

Změna částí řetězce pomocí SQLite Funkce REPLACE

REPLACE se používá k nahrazení každého řetězce vyskytujícího se na jiném řetězci řetězcem.

REPLACE(X, Y, Z) – X je vstupní řetězcový literál nebo sloupec. Y je řetězec, který se má nahradit řetězcem Z. Všimněte si, že nahradí jakýkoli výskyt řetězce Y znakem Z, nikoli pouze jedním.

Příklad:

V následujícím dotazu nahradíme všechny výskyty řetězce „xx“ řetězcem „SQLite"

SELECT REPLACE('xx is very lightweight, xx is easy to learn', 'xx', 'SQLite');

Tohle vám dá

• Funkce replace nahradila všechny řetězce „xx“ řetězcem „SQLite".
• Výstup tedy bude „SQLite je velmi lehký, SQLite je snadné se naučit“.

Ořezávání prázdných míst pomocí SQLite Funkce TRIM

TRIM odstraní prázdná místa ze začátku nebo z konce řetězce. Neodstraní žádné mezery uprostřed řetězce, pouze od začátku nebo od konce.

Příklad:

V následujícím dotazu použijeme funkci TRIM k odstranění prázdných mezer ze začátku a konce řetězce.

Všimněte si, že operátor zřetězení „||“ se používá k přidání dalšího „;“ na konec řetězce. Abychom vám ukázali, že v černém okně byla odstraněna prázdná místa.

SELECT TRIM('      SQLite is easy to learn        ') || ';';

To vám dá:

Všimněte si, jak byly prázdné mezery odstraněny ze začátku a z konce řetězce a nahrazeny ' ; ' na konci.

Čtení absolutních hodnot s SQLite Funkce ABS

Funkce ABS vrací absolutní hodnotu číselné hodnoty. Absolutní hodnota je číselná hodnota bez kladného nebo záporného znaménka. ABS(X) vrací hodnotu závislou na hodnotě X následovně:

• Absolutní hodnota X, pokud X je číselná hodnota.
• Hodnota NULL, pokud X je hodnota null.
• Hodnota „0.0“, pokud X je řetězec.

Příklad:

V následujícím dotazu se pokusíme získat absolutní hodnotu řetězce, čísla a nulové hodnoty pomocí funkce ABS:

SELECT ABS(-2), ABS(+2), ABS('a string'), ABS(null);

To vám dá:

Výsledky jsou následující:

• ABS(-2), ABS(+2) vrátí 2, protože 2 je absolutní hodnota pro „-2“ i „+2“.
• ABS('řetězec') vrátí „0.0“, protože jste předali řetězcovou hodnotu, nikoli číselnou hodnotu.
• ABS(null) vrátí hodnotu null, protože jste pro něj předali hodnotu null.

Zaokrouhlení hodnot pomocí funkce ROUND SQLite

Desetinná čísla jsou čísla s plovoucí desetinnou čárkou „.“ na něm například „20.5“, „8.65“. Část nalevo od plovoucí desetinné čárky se nazývá celočíselná část a část napravo od plovoucí desetinné čárky je desetinná část.

Celá čísla jsou čísla bez pohyblivé řádové čárky. Například „20“, „8“.

ROUND(X) převede desetinné hodnoty nebo sloupce X pouze na celá čísla. Část celého čísla, která je částí nalevo od desetinné čárky, bude vrácena a všechny číslice napravo od desetinné čárky budou odstraněny.

Příklad:

V následujícím dotazu vyzkoušíme funkci ROUND s různými možnostmi:

SELECT ROUND(12.4354354), ROUND(12.4354354, 2), ROUND(NULL), ROUND('a string');

To vám dá:

Výsledek je následující:

• ROUND(12.4354354) – vrací hodnotu „12“, protože jste nezadali počet číslic. Tak, SQLite odstranil celou desetinnou část.
• ROUND(12.4354354, 2) – vrátí hodnotu „12.44“, protože jste zaokrouhlili pouze na 2 číslice, což jsou dvě číslice zprava od desetinné čárky.
• ROUND(NULL) – vrátí hodnotu null, protože jste mu předali hodnotu null.
• ROUND('a string') – vrací hodnotu „0.0“, protože jste mu předali řetězcovou hodnotu.

Nalezení datového typu výrazu pomocí funkce TYPEOF

Pokud chcete zjistit typ sloupce nebo literálovou hodnotu, můžete k tomu použít funkci TYPEOF.

Funkce TYPEOF(X) ze svého názvu vrací datový typ výrazu X. Vrací řetězcovou hodnotu označující datový typ X. Vrací hodnotu založenou na datových typech, ať už jde o hodnotu „NULL“, skutečné, textové, celé číslo, … atd.

Příklad:

V následujícím dotazu vyzkoušíme funkci TYPEOF s různými typy doslovných hodnot:

SELECT TYPEOF(null), TYPEOF(12), TYPEOF(12.5), TYPEOF('a string');

To vám dá:

Vrácené výsledky jsou následující:

• TYPEOF(null) – vrátí hodnotu null, protože jste mu předali hodnotu null.
• TYPEOF(12) – vrátí celé číslo, protože 12 je celé číslo.
• TYPEOF(12.5) – vrací REAL, protože 12.5 je reálné číslo.
• TYPEOF('řetězec') – vrátí text, protože „řetězec“ je text.

Nalezení posledního vloženého záznamu s SQLite LAST_INSERT_ROWID

SQLite přiřadí klíč celého čísla (Id) pro všechny řádky ve všech tabulkách. Toto číslo se používá k jednoznačné identifikaci těchto řádků.

Když vložíte nový řádek do tabulky, SQLite přiřadí rowid jedinečnou hodnotu.

Pokud má tabulka primární klíč deklarovaný pouze v jednom sloupci a tento sloupec je datového typu INTEGER, bude tato hodnota sloupce použita jako rowid.

Funkce LAST_INSERT_ROWID() vrací ROW_ID posledního vloženého řádku v libovolné tabulce v databázi. Všimněte si, že to nebere žádný operand.

Příklad:

V následujícím příkladu spustíme funkci LAST_INSERT_ROWID() takto:

SELECT LAST_INSERT_ROWID();

To vám dá:

LAST_INSERT_ROWID() vrátí 0, protože do žádné tabulky v aktuálním databázovém připojení nejsou vloženy žádné řádky.

Nyní vložíme nového studenta a po příkazu insert znovu spustíme funkci LAST_INSERT_ROWID() takto:

INSERT INTO Students VALUES(11, 'guru', 1, '1998-10-12');
SELECT LAST_INSERT_ROWID();

To vám dá:

Poté, co jsme vložili nového studenta s ID 11, LAST_INSERT_ROWID() vrátí poslední vložené ID řádku, které je 11.

Získání verze vašeho SQLite knihovna

Chcete-li získat verzi vašeho SQLite knihovny, musíte zavolat funkci SQLITE_VERSION().

Příklad:

Pojďme zjistit, jaká je verze SQLite používáme spuštěním následujícího příkazu:

SELECT SQLITE_VERSION();

To vám dá:

SQLITE_VERSION() vrací 3.9.2, což je verze SQLite používáme.

Vytváření uživatelsky definovaných funkcí a agregace uživatelsky definovaných funkcí

Pro některé uživatele je SQLite základní funkce jsou omezené. Možná budou muset přidat vlastní funkce pro své vlastní potřeby.

SQLite nepodporuje vytváření uživatelsky definovaných funkcí. Na rozdíl od jiných systémů pro správu databází v nich nemůžete vytvářet funkce definované uživatelem SQLite přímo.

Funkci však můžete vytvořit pomocí programovacích jazyků, jako je C#, C, PHP nebo C++ a připojil tuto funkci k jádru SQLite funkce v SQLite samotnou knihovnu pomocí funkce „sqlite3_create_function“. Poté je můžete znovu použít ve své databázi.

Příklad:

V následujícím příkladu vytvoříme uživatelem definovanou funkci pomocí programovacího jazyka C# a přidáme ji do SQLite funkce:

[SQLiteFunction(Name = "DoubleValue", Arguments = 1, FuncType = FunctionType.Scalar)]
public class DoubleValue: SQLiteFunction
{
    public override object Invoke(object[] args)
    {
        return args[0] * 2;
    }
}

Tento fragment kódu je zapsán v Programovací jazyk C#; vytvoří funkci C# pomocí kódu C#.

Název funkce bude „Double„Hodnota“ bere jeden parametr a vrací jeho hodnotu vynásobenou dvěma.

Všimněte si, že C# tuto funkci automaticky přidá SQLite. Vše, co potřebujete, je zkompilovat a spustit tento kód. Potom C# přidá tuto funkci se stejným názvem do seznamu SQLite funkcí. Poté můžete tuto funkci použít v SQLite.

Stejným způsobem pomocí programovacích jazyků jako C#, C, PHP, popř C++, můžete také vytvářet agregované uživatelsky definované funkce. Které se používají k rozšíření agregačních funkcí v SQLite a použít je k vytvoření vlastních agregačních funkcí.

Nejčastější dotazy