Use the LIKE clause in golang x SQLite3


I learned the basics of Go language for a week or two and created a simple web application with CRUD function. I couldn't find an article that would lead to a solution even if I stumbled (LIKE clause) by operating the database in the process. I decided to write this article. The above article describes the basic database operations. Since this is my first time writing a Qiita article, I will only write about the LIKE phrase that I stumbled upon.


Version: go 1.15.3



Advance preparation

Install golang, SQLite3, go-sqlite3

Table creation and contents

First, create a table

package main

import (
	_ ""

var DbConnection *sql.DB

type Uset struct {
	Id   int
	Name string
	Age  int

func main() {
	DbConnection, err := sql.Open("sqlite3", "../example.sql")
	if err != nil {
	defer DbConnection.Close()
	name STRING,
	age INT)`
	_, err = DbConnection.Exec(cmd)
	if err != nil {

$ sqlite3 example.sql
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> .table
Data contents
sqlite> select * from user;

I put 5 records. From the left, id name age

LIKE clause

Successful code

cmd := "SELECT * FROM user WHERE name LIKE ?"
rows, err := Dbconnection.Query(cmd, "%yamada%")
if err != nil {
defer rows.Close()
var users []User
for rows.Next() {
	var user User
	err = rows.Scan(&user.Id, &m.Name, &m.Age)
	if err != nil {
users = append(users, user)

$ go run main.go
[{1 yamada 20}]

#### Code that didn't work ``` cmd := "SELECT * FROM user WHERE name LIKE '%?%'" //← here rows, err := DbConnection.Query(cmd, "yamada")

//result go run main.go []

 I'm passing a value to the "?" Part
 Instead of enclosing it in "%%" first, I had to pass the value together with "%" like ""% yamada% "".
 I found an article about the LIKE clause of Python x SQLite and used it as a reference.
 At terminals etc.

sqlite> select * from user where name like '%yamada%'; 1|yamada|20

 I can go.
#### Integers can do the same

cmd := "SELECT * FROM user WHERE age LIKE ?" rows, err := DbConnection.Query(cmd, "%1%")

//result [{3 suzuki 19} {4 tanaka 15}]

## Other usage patterns

#### When retrieving a record starting with any string

cmd := "SELECT * FROM user WHERE name LIKE ?" rows, err := DbConnection.Query(cmd, "y%")

//result [{1 yamada 20} {2 yamamoto 22}]

#### Get the data that "m" comes twice

cmd := "SELECT * FROM user WHERE name LIKE ?" rows, err := DbConnection.Query(cmd, "%m%m%")

//result [{2 yamamoto 22} {5 miyamoto 30}]

#### Get data with columns of arbitrary length (6 characters in sample)

cmd := "SELECT * FROM user WHERE name LIKE ?" rows, err := DbConnection.Query(cmd, "______")

//result [{1 yamada 20} {3 suzuki 19} {4 tanaka 15}]

## Finally
 I don't know if anyone will stumble in the same way, but I hope it helps someone. (I wanted to write the details of the code, but I'm sorry it took a long time because I'm new to it.)
 When I actually created a simple app, I realized that golang is not as informative as Rails.
 In the future, I would like to write an article about what I noticed while learning golang.
 Thank you for watching until the end.

Recommended Posts

Use the LIKE clause in golang x SQLite3
How to use the exists clause in Django's queryset
How to use SQLite in Python
Use the type features evolved in Sphinx-2.4
Resolve the Address already in use error
Use the command previously entered in IDLE
How to use the C library in Python
Use the latest pip in a virtualenv environment
Get the X Window System window title in Python
[Algorithm x Python] How to use the list
Use the LibreOffice app in Python (3) Add library
Use pygogo to get the log in json.
In SQLite3, add just two lines and use the extension library (extended SQL function)!
Sqlite in python
Download the file while viewing the progress in Python 3.x
Linux is something like that in the first place
Use Cursur that closes automatically with sqlite3 in Python
Use the CASA Toolkit in your own Python environment
[Golang] Specify an array in the value of map
Reasons to use long type in SQLite3 (C # Mono.Data.Sqlite)
Let's use the open data of "Mamebus" in Python
How to use the model learned in Lobe in Python
I want to use the R dataset in python
I can't use the darknet command in Google Colaboratory!