Use the LIKE clause in golang x SQLite3

Introduction

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.

https://qiita.com/init/items/2edfc7acf11234e5b1aa 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.

environment

Version: go 1.15.3

db：SQLite3

PC：Mac

Advance preparation

Install golang, SQLite3, go-sqlite3

• The installation procedure is also described in detail in the previous article. https://qiita.com/init/items/2edfc7acf11234e5b1aa

Table creation and contents

First, create a table

package main

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
	"log"
)

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 {
		log.Fatalln(err)
	}
	defer DbConnection.Close()
	
	cmd := `CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY,
	name STRING,
	age INT)`
	_, err = DbConnection.Exec(cmd)
	if err != nil {
		log.Fatalln(err)
	}
}

result

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

Data contents

sqlite> select * from user;
1|yamada|20
2|yamamoto|22
3|suzuki|19
4|tanaka|15
5|miyamoto|30
sqlite> 

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 {
	log.Println(err)
}
defer rows.Close()
var users []User
for rows.Next() {
	var user User
	err = rows.Scan(&user.Id, &m.Name, &m.Age)
	if err != nil {
		log.Println(err)
	}
users = append(users, user)

fmt.Println(users)

result

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

//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.
<br>
 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.