MySQL full indexed text search - just like a real database!!

• As of My Sql 3.something you can now add text indexes to your columns and do full text searches with relevance ranking! text indexes are fantastic and allow more efficient searching and categorisation of text. The relevance ranking feature is rather good too and gives you a much better idea of how your search performed. 
•  
• Issues to note are, this example uses relevance ranking as the criteria for returning results, if you take that condition out you will return every result in the database with a relevance score of 0. 
•  
• The default MySQL settings do not bother to index search terms of less than 4 characters therefore searching for anything less will bring back no results. This can be altered by setting "set ft_min_word_len =1" in your mysql config. This will result in a performance hit.  
•  
• Text indexes in large data sets can be quite processor intensive so you might also consider limiting your result set. 
•  
• ALTER TABLE `database`.`table_name` 
• ENGINE = MyISAM; 
•  
• ALTER TABLE table_name 
• MODIFY column_name TEXT; 
•  
• ALTER TABLE table_name 
• ADD FULLTEXT(column_name); 
•  
• SELECT *, 
• MATCH(column_name) 
• AGAINST ('search term') 
• AS score 
• FROM table_name 
• HAVING score > 0 
• ORDER BY score DESC;

sql

1. sql 
2.  
3. SERVEROUTPUT ON 
4.  
5. declare 
6.    result_limit number := 10; 
7.    current_query_no number := 1; 
8.    --table_n varchar2(30) := 'brian.cran1400_der_results'; 
9.    --cursor all_results is select * from brian.cran1400_der_results; 
10.    cursor result_list(counter number) is select * from brian.cran1400_der_results where queryno = counter order by score DESC, docno ASC; 
11.    --cursor sub_list is select * from brian.cran1400_der_results where  
12.    --select all results from table where query number is less than 10 
13.    inner_min number := 1; 
14.    inner_max number := 10; 
15.    total_precision number := 0; 
16.  
17.  
18. begin 
19.  
20. for result_record IN result_list(current_query_no) loop 
21. --loops through the result list in the cursor (all records 1 - 10) 
22.  
23.    for counter_one in inner_min..inner_max loop 
24.          -- do stuff here 
25.          current_query_no := counter_one; 
26.           
27.          if result_record.rn = 'R' then total_precision := total_precision + 1; 
28.          dbms_output.put_line (total_precision); 
29.          end if; 
30.  
31.  
32.  
33.    end loop; 
34. end loop; 
35. end; 
36.  
37.  
38.  

sql server - set db offline

if you can't track down all the life connections and you need to force changes:

use master
alter database db_name set offline with rollback immediate

SQL - Composite Primary Keys

Useful Regex for CSV to SQL

As a point of reference

how to convert a comma delimited file to an SQL insert
Assuming your data is the following

'key','value','0'
'key','value','1'

In notepad++ the syntax for search and replace would be as follows:
search: ('[^']+','[^']+','[^']+')
replace: (\1)
syntax varies depending on the flavour of regex

which can then have an insert statement added and...:
insert into table (key_value, value_blah, personal_id)
values
('key','value','0')
('key','value','1')

deleteing columns in Textpad
INSERT INTO "IB_RESOURCE_MESSAGE" (IB_MESSAGE_ID,IB_VALUE,IB_KEY,IB_BUNDLE_ID) VALUES (0,'Welcome #{identity.username}','loginSucceeded',0)

to delete the first value column (1, .... use the following
([0-9]+,
the plus will search for recurring instances of the pattern [0-9]

Mulitple OR conditions in SQL

based on

ORDER_TRANSACTION
__________
1|ORDER__|
2|CREDIT__|
3|REFUND_|
4|PAYMENT|

Instead of the following:

SELECT
ot
FROM
OrderTransaction
AS
ot
WHERE
ot.transactionType.transactionTypeDesc = 'Credit'
OR
ot.transactionType.transactionTypeDesc = 'Refund'
OR
ot.transactionType.transactionTypeDesc = 'Payment'

you could use:

SELECT
ot
FROM
OrderTransaction
AS
ot
WHERE
ot.transactionType.transactionTypeDesc IN ('Credit','Refund','Payment')

or:

SELECT
ot
FROM
OrderTransaction
AS
ot
WHERE
ot.transactionType.transactionTypeDesc NOT IN ('Order')

EJB QL Query Example

1.          SELECT 
2.             SUM(p.orderTransaction.transAmount) 
3.          FROM     
4.             Payment AS p 
5.          JOIN 
6.             p.memberPaymentMethod AS mpm 
7.          JOIN 
8.             mpm.paymentMethod AS pm 
9.          JOIN 
10.             p.orderTransaction as ot 
11.          WHERE 
12.             ot.currency.currencyCode = 'GBP' 
13.          AND 
14.             mpm.creationDate >= '01/01/2008' 
15.          AND 
16.             mpm.creationDate <= '01/07/2009' 
17.          AND  
18.           (p.authorisationCode IS NOT NULL OR pm.paymentMethodId NOT IN (8,4))

MYSQL UNION SELECT NULL

1. SELECT * FROM  
2. crh_houses  
3. LEFT JOIN  
4. crh_p_tags 
5. ON  
6. crh_houses.house_id = crh_p_tags.house_id 
7. WHERE 
8. crh_houses.house_id = 3  
9. UNION DISTINCT 
10. SELECT  
11. *,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL 
12. FROM  
13. crh_p_tags WHERE crh_p_tags.house_id = 3

MySql code - left join group by

1. SELECT  
2. * 
3. FROM  
4. holiday_dates  
5. LEFT JOIN 
6.  
7. holiday_rentals  
8. ON  
9. holiday_rentals.week = holiday_dates.week 
10.  
11. AND holiday_rentals.house_id = 3 
12.  
13. GROUP BY holiday_dates.week