LIKE Predicate
-
Hi guys, I'm struggling with something conceptually that maybe somebody would be able to help me with. I'm writing an application to match vehicles in one database to vehicle in another database. As you can imagine there are difference all over the place so there will be a lot of manual matching to be done but I'm doing my best to cut down on the amount of manual work where possible. The LIKE predicate has definitely been my friend here, but how can I make it work the other way around, i.e. instead of C_Model LIKE '%Fiesta%' I could do with 'Fiesta' LIKE %C_Model%. If you know what I mean? For instance I can match this:
DB1 DB2
'Fiesta' -> 'Fiesta 1'
-> 'Fiesta 2'
-> 'Fiesta 3'...but I don't know how to go this way:
DB1 DB2
'306 -97' -> '306'
'306 97-' -> '306'Cheers, Chris Chambers.
-
Hi guys, I'm struggling with something conceptually that maybe somebody would be able to help me with. I'm writing an application to match vehicles in one database to vehicle in another database. As you can imagine there are difference all over the place so there will be a lot of manual matching to be done but I'm doing my best to cut down on the amount of manual work where possible. The LIKE predicate has definitely been my friend here, but how can I make it work the other way around, i.e. instead of C_Model LIKE '%Fiesta%' I could do with 'Fiesta' LIKE %C_Model%. If you know what I mean? For instance I can match this:
DB1 DB2
'Fiesta' -> 'Fiesta 1'
-> 'Fiesta 2'
-> 'Fiesta 3'...but I don't know how to go this way:
DB1 DB2
'306 -97' -> '306'
'306 97-' -> '306'Cheers, Chris Chambers.
You may now get ready to kick yourself.... You have no problem getting
DB2 like 'Fietsa%'what is wrong with getting Db1 like '306%'Never underestimate the power of human stupidity RAH
-
You may now get ready to kick yourself.... You have no problem getting
DB2 like 'Fietsa%'what is wrong with getting Db1 like '306%'Never underestimate the power of human stupidity RAH
Yeah, maybe I should have explained myself better. Take this statement from the second databases DataSet class:
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%'");
Is there an expression I can use to try to further enhance this expression to help me retrieve the records where C_Model is like the model passed in? If '306 -97' is passed in and the models table contains '306' rows how can I get to those rows? Like I say, it's fine if I pass in 'Fiesta' and the models table contains 'Fiesta 1', 'Fiesta 2', 'Fiesta 3' etc. The more I think about this the more I'm beginning to think it's not possible. I just don't see how I can select rows where a certain field matches a substring of a literal string.
-
Hi guys, I'm struggling with something conceptually that maybe somebody would be able to help me with. I'm writing an application to match vehicles in one database to vehicle in another database. As you can imagine there are difference all over the place so there will be a lot of manual matching to be done but I'm doing my best to cut down on the amount of manual work where possible. The LIKE predicate has definitely been my friend here, but how can I make it work the other way around, i.e. instead of C_Model LIKE '%Fiesta%' I could do with 'Fiesta' LIKE %C_Model%. If you know what I mean? For instance I can match this:
DB1 DB2
'Fiesta' -> 'Fiesta 1'
-> 'Fiesta 2'
-> 'Fiesta 3'...but I don't know how to go this way:
DB1 DB2
'306 -97' -> '306'
'306 97-' -> '306'Cheers, Chris Chambers.
I think Mycroft is right. And if all else fails, you could still use substrings and go for an exact match of the first N characters. :)
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
-
Yeah, maybe I should have explained myself better. Take this statement from the second databases DataSet class:
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%'");
Is there an expression I can use to try to further enhance this expression to help me retrieve the records where C_Model is like the model passed in? If '306 -97' is passed in and the models table contains '306' rows how can I get to those rows? Like I say, it's fine if I pass in 'Fiesta' and the models table contains 'Fiesta 1', 'Fiesta 2', 'Fiesta 3' etc. The more I think about this the more I'm beginning to think it's not possible. I just don't see how I can select rows where a certain field matches a substring of a literal string.
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");
This answer is too simple I must be missing something in your question. I use stored procs for 99.9% of my database work and therefore can design the SQL to do exactly what I want in QA/EM. I suggest you do this to get the result you want and then move it to a string if that is where you want to have you SQL. Also look into parameterised queries, you current model is subject to sql injection.
Never underestimate the power of human stupidity RAH
-
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");
This answer is too simple I must be missing something in your question. I use stored procs for 99.9% of my database work and therefore can design the SQL to do exactly what I want in QA/EM. I suggest you do this to get the result you want and then move it to a string if that is where you want to have you SQL. Also look into parameterised queries, you current model is subject to sql injection.
Never underestimate the power of human stupidity RAH
Hi Mycroft,
Mycroft Holmes wrote:
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");
This won't work because the '306' is what's actually in C_Model; at the point this statement is executed I don't actually know what that is. I just used that '306' string as a typical example. To be honest, the more I've thought about this the more I've decided it's a stupid thing to do in the context of my application anyway so I've abandoned the idea for now. I'm sorry if I've wasted your time. Many thanks for your help anyway. Thanks again, Chris Chambers.
-
I think Mycroft is right. And if all else fails, you could still use substrings and go for an exact match of the first N characters. :)
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
Thanks for your input Luc, much appreciated. Regards, Chris Chambers.
-
Yeah, maybe I should have explained myself better. Take this statement from the second databases DataSet class:
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%'");
Is there an expression I can use to try to further enhance this expression to help me retrieve the records where C_Model is like the model passed in? If '306 -97' is passed in and the models table contains '306' rows how can I get to those rows? Like I say, it's fine if I pass in 'Fiesta' and the models table contains 'Fiesta 1', 'Fiesta 2', 'Fiesta 3' etc. The more I think about this the more I'm beginning to think it's not possible. I just don't see how I can select rows where a certain field matches a substring of a literal string.
I'm not sure if I understand your question. I think this will do what you want:
select * from Models
where C_Model like '%306 -97%'
or '306 -97' like C_Model + '%'It won't be the fastest query in the world, but it should do what you want. It will pick out models that have 306 -97 anywhere in the model number, (e.g. '4306 -97') and it will also get all the rows with model numbers '306'. If there are any rows with model number '3' or '30' it will get those too, but that's what you seem to be asking for.
-
I'm not sure if I understand your question. I think this will do what you want:
select * from Models
where C_Model like '%306 -97%'
or '306 -97' like C_Model + '%'It won't be the fastest query in the world, but it should do what you want. It will pick out models that have 306 -97 anywhere in the model number, (e.g. '4306 -97') and it will also get all the rows with model numbers '306'. If there are any rows with model number '3' or '30' it will get those too, but that's what you seem to be asking for.
Hi David, thanks for your input. I might try that to see if it works in my application, however as you rightly said it's likely to pick up quite a few entries that I don't want so as I said to Mycroft I think I've decided not to go ahead with it. I'll let you know if that works though. Regards, Chris Chambers.
-
Hi Mycroft,
Mycroft Holmes wrote:
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");
This won't work because the '306' is what's actually in C_Model; at the point this statement is executed I don't actually know what that is. I just used that '306' string as a typical example. To be honest, the more I've thought about this the more I've decided it's a stupid thing to do in the context of my application anyway so I've abandoned the idea for now. I'm sorry if I've wasted your time. Many thanks for your help anyway. Thanks again, Chris Chambers.
Not a problem
Never underestimate the power of human stupidity RAH