2
In my bank I have 3 columns for phone, I need to know if the value is in any of the 3 fields, what is the best way to mount this select?
2 answers
4
You can use a IN "otherwise" (than normally used):
... WHERE '2345-6789' IN (tel1, tel2, tel3)
-
1
The clause is absolutely correct, but it is as if it were the other way around, it gave me vertigo. It seems like I’m looking at a picture upside down, so much is the custom of using the field on the left and fixed values on the right of the
IN. (:
3
If it’s an inaccurate search, that is, for numbers that contain a chunk of the phone, you can do so:
WHERE telefone1 like '%123%'
OR telefone2 like '%123%'
OR telefone3 like '%123%'
Being 123 the number entered by the user in the search field.
-
In Mysql I usually use CONCAT( phone1, ' ', phone2, ' ', phone3 ) LIKE '%123%' (has concat_ws too, to put the space only once, but I commented of "traditional" because it is more common in the various Dbms).
-
@Bacco had thought about the
CONCAT. I’m curious to know about the difference in performance between the two solutions. But a possible problem is that if the user can enter the separator character, there is possibility of the query return a false positive involving the end and the beginning of two phones. -
1
in fact this has to be used carefully. The ideal is to actually choose a "special" tab. Usually I associate this to a split by the separator itself, so in my case does not give side effect; On the @bfavaretto response, I really find interesting any and all alternative that saves the like, when it comes to substring. Yours I find interesting as general guidance. I need to even review some answers of mine to optimize for specific Dbms.
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.
Not directly for the case in question, but for those using a solution of the type with some auxiliary language, follow an example of how I automate searches in several fields and with several strings: http://answall.com/a/13181/70
– Bacco