|
No, there isn't. You validate it in code before inserting/updating.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Steps:
Choose the data type that it is actually going to be stored as.
Take a value and use on of the sqllite functions to convert it to a different type.
Take that value and convert it back to the original type.
If they are the same then it is valid.
So if you start with text, convert it to a number, then convert the number back to text and compare the original text and the final text.
|
|
|
|
|
rahmat1985 wrote: as we all know
We don't all know that.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I have the following table in MySQL:
table name : contacts
Field:
contact_name
company_name
service_offered
I want the user to be able to search the contact table in this way:
if the user entered
John Dell
then it should search all fields for any of the given words
so if John is the first name and Dell is company
or John Dell is the name (contains)
or John is the name and Dell is the service
etc...
can anyone tell me how can I do such SQL?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
Dear Jassim,
If you know the exact column list to search data
For Example :
1. company_name
2. first_name
3. last_name
Then you can write down your query as below :
SELECT * FROM contacts WHERE (company_name like '%'+@searchText+'%' OR first_name like '%'+@searchText+'%' OR last_name like '%'+@searchText+'%')
Secondly, If you don't know about the column list then let me know the I can write a dynamic query for you.
|
|
|
|
|
It would be something like this:
SELECT *
FROM contacts
WHERE (contact_name LIKE '%John%' OR contact_name LIKE '%Dell%')
OR (company_name LIKE '%John%' OR company_name LIKE '%Dell%')
OR (service_offered LIKE '%John%' OR service_offered LIKE '%Dell%')
Basically, you'd split the search text into separate words and dynamically build the query above to include all the words.
|
|
|
|
|
the text might be more than two or less...
so user might type something like
John
or
John Dell
or
John Dell international
so I want to be able to split all and search
Technology News @ www.JassimRahma.com
|
|
|
|
|
Yes, and that's what I said. You'll have to build this sql query dynamically on the client by looping through every word in the search text. Something like this (code just out of my mind, not tested).
string BuildQuery(string searchText) {
string sqlQuery = "SELECT * FROM contacts WHERE ";
string[] words = searchText.Split(" ".ToCharArray());
foreach(string word in words) {
sqlQuery+= string.Format("(contact_name LIKE '%{0}%' OR company_name LIKE '%{0}%' OR service_offered LIKE '%{0}%' ) OR ", word);
}
sqlQuery = sqlQuery.SubString(1, sqlQuery.Length-2);
return sqlQuery;
}
|
|
|
|
|
Jassim Rahma wrote: so I want to be able to split all and search There's a small difference to keep in mind when using this technique instead of a full-text search; you're effectively selecting ALL textfields for ALL RECORDS. There'll be no optimization on the filter, as the engine will have to check each field whether it 'contains' the request value.
That might be rather costly.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi I have to search a string into a database object. the string can be a part of SP or Function or any other sys object.
Thanks in Advance
|
|
|
|
|
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Employee%'
Vaibhav
|
|
|
|
|
Thanks Vaibhav...
Will it work for SQL 2005
|
|
|
|
|
I am not sure Please check.
Vaibhav
|
|
|
|
|
Hi I found..
SELECT * FROM all_source WHERE Lower(text) LIKE '%reportid%'
this will work for Oracle
|
|
|
|
|
If you have to do this sort of thing regularly, you might want to look at SQL Search[^]. It's free at the moment, although they might decide to charge for future versions.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi everyone - first post and question about SSAS so be gentle
I am attempting to build a cube with sample warehouse data of a handful of dimensions (including date) and one fact table. Popular data warehouse insertion (from what i've read - although I could be wrong) is based on an initial load of data then incremental updates based on the changes and the date of change. This would then present the problem of duplicate rows in the warehouse, and therefore the cube???
One of the reporting requirements from the cube is to report on a given date, what are the total financials for a given selection claims, grouped by area. If an initial then ongoing updated load of data is utilised, how would I be able to query via date (today or otherwise)? Because of the duplicate rows in the cube, would a claim be counting a financial value twice?
Any clarification needed, please let me know.
Thanks.
|
|
|
|
|
I would assume your fact table has a primary key, if you are using aggregated value you still need a unique key, just insert any records that do not exist. Or if you are working by date, query the cube for existing dates.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi - thanks for your reply.
The fact table has a composite PK - FactDateID (the date of insertion) and DimRepairID (each repair is unique in the OLTP system). These combined will give a unique reference for a row.
My issue is how I solve the issue of displaying the correct data in a cube when I choose a date. For example:
RepairID 1 and date of 20140615 is inserted into the fact table. The financial value associated with this is 10. No new row is inserted for the 16th of June, as there was no activity on this repair. However on the 17th of June, the financial value was updated to 20 by a user in the OLTP system and therefore transferred to the DW. A new row of 20140617 with RepairID of 1 and value of 20 is then inserted.
Bearing in mind that there will be many RepairIDs in the fact table and some will have changed recently and others not, how do I enable users to pick a date and see what rows were what financial values at a given point in time?
Thanks
|
|
|
|
|
At the end of the 90ties we somehow got into ORM. At the time - no matter how hard we tried - we always ended up with a messy and complicated code and filthy compromises. And it hasn't changed. But somehow frameworks like NHibernate and EntityFramework are becoming very popular.
So here is my rant.
Some time ago I worked on large projects and things were pretty predictable. You've got DB model. You generate your SQL procedure layer and your C# layer. Then you create your component / web service / wcf service / restful service and serialize results of your C# calls as POCOs.
Now try to do this with fancy NHibernate objects with auto-resolving proxy objects for related entities. It won't work. Because when objects are serialized their auto-resolving lazy evaluating proxies aren't. To solve it you duplicate /I'll write it again, for drama effect: duplicate/ your objects to create serializable POCOs. And then you create them CRUD functions on top of object models. Or even separate the entities /drama: dereference them having no two entities connected/... And, hey, you are back where you were with the stored procedures - only with lousier performance and three layers of crap on top of it.
So next time someone comes with a fancy-schmancy ORM wrapper it better already include web service / wcf service or restful service abstraction and work on top of it; rather then bellow it! Because otherwise we just off-load drudgery to the web services and call it "business layer" when in fact it is really a freaking "ORM back to stored procedures layer."
There. Hhhh...shhhh... I already feel better.
|
|
|
|
|
|
Is it possible to use your admin Harry Potter powers to move the entire thread there? I can't seem to be able to do that...please.
|
|
|
|
|
Sorry, I'm not an administrator.
|
|
|
|
|
Don't worry, think of it as a suggestion for the future.
|
|
|
|
|
Wat, are you kidding, the Lounge is exactly the place for a good rant about the tech we have to use, I suggest you clarify that with the OP.
[edit] oops I thought I was in the Lounge I'll just remove that foot![/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|