Funciones escalares#
Ultima modificación: Mar 6, 2024 | YouTube
Preparacion
[1]:
import sqlite3
from IPython.core.magic import register_cell_magic, register_line_magic
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
@register_line_magic
def sql(line):
return cur.execute(line).fetchall()
@register_cell_magic
def sqlite3script(line, cell):
cell = [l for l in cell.split("\n") if len(l) >= 2 and l[0:2] not in ["--", "##"]]
cell = "\n".join(cell)
conn.executescript(cell)
conn.commit()
Uso
[2]:
%%sqlite3script
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a, b);
INSERT INTO
t1
VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');
[3]:
%sql SELECT a, upper(b) FROM t1
[3]:
[(1, 'A'), (2, 'B'), (3, 'C')]
Funciones
[4]:
##
## abs(X)
##
%sql SELECT abs(-1)
[4]:
[(1,)]
[5]:
##
## char(X1,X2,...,XN)
## (unicode)
##
%sql SELECT char(35,42,50)
[5]:
[('#*2',)]
[6]:
##
## coalesce(X,Y,...)
## retorna una copia del primer argumento no nulo
##
%sql SELECT coalesce(NULL,1,NULL,2)
[6]:
[(1,)]
[7]:
##
## format(FORMAT,...)
## opera como la función printf del lenguage C
##
%sql SELECT format("--> %d <---", 1)
[7]:
[('--> 1 <---',)]
[8]:
##
## glob(X,Y)
## opera con wildcards
##
%sql SELECT glob("*ti*", "america latina")
[8]:
[(1,)]
[9]:
##
## ifnull(X,Y)
## retorna Y si X es NULL
##
%sql SELECT ifnull(NULL, "es nulo")
[9]:
[('es nulo',)]
[10]:
##
## iif(X,Y,Z)
## X es un booleano
##
%sql SELECT iif(TRUE, "parte then", "parte else")
[10]:
[('parte then',)]
[11]:
##
## length(X)
##
%sql SELECT length("hola mundo")
[11]:
[(10,)]
[12]:
##
## like(X,Y)
##
%sql SELECT like("hol%", "hola"), like("hol%", "mundo", "X")
[12]:
[(1, 0)]
[13]:
##
## lower(X)
##
%sql SELECT lower("HOLA MUNDO")
[13]:
[('hola mundo',)]
[14]:
##
## ltrim(X)
##
%sql SELECT ltrim(" HOLA MUNDO "), ltrim("HOLA MUNDO", "HO")
[14]:
[('HOLA MUNDO ', 'LA MUNDO')]
[15]:
##
## max(X,Y,...)
##
%sql SELECT max(1, 2, 3, 4)
[15]:
[(4,)]
[16]:
##
## min(X,Y,...)
##
%sql SELECT min(1, 2, 3, 4)
[16]:
[(1,)]
[17]:
##
## nullif(X,Y)
## retorna el primer argumento si X y Y son diferentes
## y NULL en caso contrario
##
%sql SELECT nullif('a', 'b'), nullif('a', 'a')
[17]:
[('a', None)]
[18]:
##
## random()
##
%sql SELECT random()
[18]:
[(-1042884606900829196,)]
[19]:
##
## replace(X,Y,Z)
## reemplaza el string Y por Z en cada aparción de Y en X
##
%sql SELECT replace("-11-11-", "11", "000")
[19]:
[('-000-000-',)]
[20]:
##
## round(X)
## round(X,Y)
##
%sql SELECT round(1.2345678), round(1.2345678, 3)
[20]:
[(1.0, 1.235)]
[21]:
##
## rtrim(X)
## rtrim(X, Y)
##
%sql SELECT rtrim(" HOLA MUNDO "), rtrim("HOLA MUNDO", "NDO")
[21]:
[(' HOLA MUNDO', 'HOLA MU')]
[22]:
##
## sign(X)
##
%sql SELECT sign(10), sign(-10), sign(0)
[22]:
[(1, -1, 0)]
[23]:
##
## substr(X,Y,Z)
## substr(X,Y)
## substring(X,Y,Z)
## substring(X,Y)
##
%sql SELECT substr("hola mundo cruel!", 4), substr("hola mundo cruel!", 4, 3)
[23]:
[('a mundo cruel!', 'a m')]
[24]:
%sql SELECT substring("hola mundo cruel!", 4), substring("hola mundo cruel!", 4, 3)
[24]:
[('a mundo cruel!', 'a m')]
[25]:
##
## trim(X)
## trim(X,Y)
##
%sql SELECT trim(" hola mundo "), trim("--hola mundo--", "-")
[25]:
[('hola mundo', 'hola mundo')]
[26]:
##
## unicode(X)
##
%sql SELECT unicode("A")
[26]:
[(65,)]
[27]:
##
## upper(X)
##
%sql SELECT upper("hola mundo")
[27]:
[('HOLA MUNDO',)]