002-几种常见数据库 like 的替代方法

几种常见数据库 like 的替代方法

1、包含2、不包含3、开头值为4、开头值不为5、结尾值为6、结尾值不为 Oracle、MySQL、PostGreSQL、SQL SERVER、HIVE 数据库中 like 替换。

1、包含

like '%xxx%' # Oracle instr(str,substr) > 0 # MySQL locate(substr,str) > 0 position(substr in str) > 0 instr(str,substr) > 0 # PostGreSQL position(substr in str) > 0 # SQL SERVER charindex(substr,str) > 0 # HIVE locate(substr,str) > 0 instr(str,substr) > 0

2、不包含

like '%xxx%' # Oracle instr(str,substr) <= 0 # MySQL locate(substr,str) <= 0 position(substr in str) <= 0 instr(str,substr) <= 0 # PostGreSQL position(substr in str) <= 0 # SQL SERVER charindex(substr,str) <= 0 # HIVE locate(substr,str) <= 0 instr(str,substr) <= 0

3、开头值为

like '%xxx%' # Oracle instr(str,substr) = 1 # MySQL locate(substr,str) = 1 position(substr in str) = 1 instr(str,substr) = 1 # PostGreSQL position(substr in str) = 1 # SQL SERVER charindex(substr,str) = 1 # HIVE locate(substr,str) = 1 instr(str,substr) = 1

4、开头值不为

like '%xxx%' # Oracle instr(str,substr) != 1 # MySQL locate(substr,str) != 1 position(substr in str) != 1 instr(str,substr) != 1 # PostGreSQL position(substr in str) != 1 # SQL SERVER charindex(substr,str) != 1 # HIVE locate(substr,str) != 1 instr(str,substr) != 1

5、结尾值为

like '%xxx%' # Oracle instr(str,substr) = length(str) # MySQL locate(substr,str) = char_length(str) position(substr in str) = char_length(str) instr(str,substr) = char_length(str) # PostGreSQL position(substr in str) = length(str) # SQL SERVER charindex(substr,str) = len(str) # HIVE locate(substr,revserse(str)) = 1 instr(revserse(str),substr) = 1

6、结尾值不为

like '%xxx%' # Oracle instr(str,substr) < length(str) # MySQL locate(substr,str) < char_length(str) position(substr in str) < char_length(str) instr(str,substr) < char_length(str) # PostGreSQL position(substr in str) < length(str) # SQL SERVER charindex(substr,str) < len(str) # HIVE locate(substr,revserse(str)) != 1 instr(revserse(str),substr) != 1