在Go中使用查询Args获取记录

Question

I Need help for fetch records from table using Go.

My Problem is that i'm writing MySQL query and add another where clause i.e HPhone number, Here HPhone number inserted in data base with format like 999-999-9999.And i passed this HPhone Number in format like 9999999999. which is not matching with correct data base field value. And i used SUBSTRING for add hyphen between numbers but it does not get records but when i passed like 999-999-9999 without SUBSTRING it return records.

Here i demonstrate how i used this.

strQry = `SELECT * from table WHERE Depot = ?`

if HPhone != "" {
    strQry += ` AND HPhone = ?`
}

queryArgs := []interface{}{RouteAvailability.Depot}

if HPhone != "" {
    queryArgs = append(queryArgs, "SUBSTRING("+HPhone+",1,3)"+"-"+"SUBSTRING("+HPhone+",4,3)"+"-"+"SUBSTRING("+HPhone+",7,4)")
}

Help would be appreciated. Thanks in advance.

Answer 1

Instead of SUBSTRING you can use REPLACE like so:

queryArgs := []interface{}{RouteAvailability.Depot}

if HPhone != "" {
    strQry += ` AND REPLACE(HPhone, '-', '') = ?`
    queryArgs = append(queryArgs, HPhone)
}

---

If possible I would suggest you normalize your data, i.e. decide on a canonical format for a particular data type and everytime your program receives some input that contains that data type you format it into its canonical form, that way you can avoid having to deal with SUBSTRING, or REPLACE, or multiple inconsistent formats etc.