开发者

Could SQL query for muliple fields using like operator be written differently?

开发者 https://www.devze.com 2022-12-20 13:39 出处:网络
I\'m don\'t have much experience in writing SQL so maybe this is a fairly trivial question but right now I have a SQL query where need to do a \'like\' on multiple fields, at present I do:

I'm don't have much experience in writing SQL so maybe this is a fairly trivial question but right now I have a SQL query where need to do a 'like' on multiple fields, at present I do:

select * 
from tableX  
where col1 like '10%' 
   or col2 like '10%' 
   or col3 like '10%'  
   or col4 like '10%'  
   or col5 like '10%'  
   or col6 like '10%' 

Is there di开发者_如何学Pythonfferent, that is simpler or better, way of writing the SQL?

Thanks


select * from tableX where col1 like '10%'
UNION
select * from tableX where col2 like '10%'
UNION
select * from tableX where col3 like '10%'
...

If you were comparing one col against multiple values, then there are other options, such as

SELECT *
FROM
   tableX t1
   JOIN
   tableFilter TF ON t1.col LIKE TF.FilterValue


By better do you mean faster?

I expect the following could be faster but sql might already optimize it:

select * 
from tableX  
where substring(col1,0,2) = '10' 
   or substring(col2,0,2) = '10' 
   or substring(col3,0,2) = '10' 
   or substring(col4,0,2) = '10' 
   or substring(col5,0,2) = '10' 
   or substring(col6,0,2) = '10' 

a lot depends on what you are doing, if you are doing this a lot and each column starts with a two character code then you might want to split that value into it's own column.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号