MySQL fuzzy matching 1, 11111 similar string problems

Problem analysis

Today I encountered a fuzzy matching problem , Generally speaking, go directly LIKE"%1%" That's it .

SELECT * FROM AFTFACT_TIMELY_OPT_DATADIVISION WHERE SCOPE_USER  LIKE "%1%"

But the following , I found that there was no 1 The data of , But the lack was found , It turns out there are 11,111 This kind of data , because LIKE It's fuzzy matching , There are not many restrictions , So that's what happened ,111,11 Also found .
Now the problem has been found , To solve the problem .

Problem solving

CONCAT The function is to connect multiple strings into one string , At this time, we need to think about concat The problem of splicing , Is it possible to add something spliced? We can achieve this function , Then we get the following problem solving and implementation .

 SELECT * FROM AFTFACT_TIMELY_OPT_DATADIVISION WHERE CONCAT(",",SCOPE_USER,",") LIKE "%,1,%"

Just add English before and after the execution field , In this way, the last situation will not occur after the implementation , It is also the result we want to find out .

summary

1、CONCAT function ： Concatenate multiple strings into a single string .
2、 grammar ：concat(str1, str2,…)
3、 Compare the usage of concatenated strings in other databases Oracle String connection uses “||” String splicing , How it is used and MSSQLServer The plus sign in “+” equally SELECT ‘ Your room number, :’ || NO || ‘ The unit price :’ || price FROM DUAL; Of course ,Oracle Chinese vs CONCAT() Functions are also supported
matters needing attention ：
1、 If CONCAT The value connected in is not a string ,Oracle Will try to convert it to a string
2、 And MYSQL Of CONCAT() Functions are different ,Oracle Of CONCAT() Function only supports two parameters , Splicing of more than two strings is not supported
3、Oracle More than two parameters should be spliced in , Still have to use “||” Or you can use multiple CONCAT() Nested functions use

for example ：

SELECT CONCAT(CONCAT(CONCAT('CSDN:',NO),' Test it :'),price) FROM DUAL;