select * where clause
-
i want a transact sql for select queries which returns all rows that include something like here: table1 code, name, address, ?? , ?? 1 , john , toronto , .. 2 , mike , new york , .. 3 , alec , john city, .. ... select * from table1 where includes 'john' result is: 1 , john , toronto 3 , alec , john city [note1: i do not know field names] [note2: fields are in different data types] [ like : int, numeric, float, char, text, image ....] [note3: full text indexing is appropriate for string fields,not for all types]
-
i want a transact sql for select queries which returns all rows that include something like here: table1 code, name, address, ?? , ?? 1 , john , toronto , .. 2 , mike , new york , .. 3 , alec , john city, .. ... select * from table1 where includes 'john' result is: 1 , john , toronto 3 , alec , john city [note1: i do not know field names] [note2: fields are in different data types] [ like : int, numeric, float, char, text, image ....] [note3: full text indexing is appropriate for string fields,not for all types]
amraouf wrote:
[note1: i do not know field names]
Then you will need to find out.
amraouf wrote:
[note2: fields are in different data types]
It is a bit difficult to do a string match on a non-string column.
amraouf wrote:
[note3: full text indexing is appropriate for string fields,not for all types]
Then why not create a string column with all the searchable stuff in it (including string representations of non-string column) and index that. If you make the column name consistent across all tables then you don't need to know each individual column name, because all tables have it.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
amraouf wrote:
[note1: i do not know field names]
Then you will need to find out.
amraouf wrote:
[note2: fields are in different data types]
It is a bit difficult to do a string match on a non-string column.
amraouf wrote:
[note3: full text indexing is appropriate for string fields,not for all types]
Then why not create a string column with all the searchable stuff in it (including string representations of non-string column) and index that. If you make the column name consistent across all tables then you don't need to know each individual column name, because all tables have it.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
amraouf wrote:
its a bit difficult to change the structure of a database
How come?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
amraouf wrote:
its a bit difficult to change the structure of a database for such a query !
No it isn't. This is a simple change. 1. Add the extra column on each table that requires it. 2. Add triggers to each table to update the new column each time there is an insert or update If you have difficulty figuring out how to change the database structure from one thing to another then you might want to buy a book on refactoring databases. There is a very good one published by Addison Wesley.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
amraouf wrote:
its a bit difficult to change the structure of a database for such a query !
No it isn't. This is a simple change. 1. Add the extra column on each table that requires it. 2. Add triggers to each table to update the new column each time there is an insert or update If you have difficulty figuring out how to change the database structure from one thing to another then you might want to buy a book on refactoring databases. There is a very good one published by Addison Wesley.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
Colin Angus Mackay wrote:
This is a simple change. 1. Add the extra column on each table that requires it. 2. Add triggers to each table to update the new column each time there is an insert or update
Thank you, I was too lazy to go to that extent earlier :-O
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
amraouf wrote:
its a bit difficult to change the structure of a database for such a query !
No it isn't. This is a simple change. 1. Add the extra column on each table that requires it. 2. Add triggers to each table to update the new column each time there is an insert or update If you have difficulty figuring out how to change the database structure from one thing to another then you might want to buy a book on refactoring databases. There is a very good one published by Addison Wesley.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
i want a transact sql for select queries which returns all rows that include something like here: table1 code, name, address, ?? , ?? 1 , john , toronto , .. 2 , mike , new york , .. 3 , alec , john city, .. ... select * from table1 where includes 'john' result is: 1 , john , toronto 3 , alec , john city [note1: i do not know field names] [note2: fields are in different data types] [ like : int, numeric, float, char, text, image ....] [note3: full text indexing is appropriate for string fields,not for all types]
-
Thank you very much Of course I designed the database and I am the first one who can change or update it but you know in this phase of project i can not push my time on this matter
amraouf wrote:
Of course I designed the database and I am the first one who can change or update it but you know in this phase of project i can not push my time on this matter
When I put a project together I figure out what are the bigger risks and do them up front so I've got as much time as possible to make changes if my ideas don't work out. It sounds to me that you need to be identifying those risks before it gets too late. If the feature is not a "Must have" then it can be dropped. If the feature is required then the solution I gave is probably the quickest route to it being implemented.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
And this acounts for the numeric types how exactly? Also, it is hardly an optimised solution - The database is forced to scan the entire table each time.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website