Tips and Tricks in a world of Mix

Posts tagged ‘sql’

useful sql

Backup data

If NEW_TABLE already exists then …

insert into new_table select * from old_table
/

If you want to create NEW_TABLE based on the records in OLD_TABLE …

create table new_table as select * from old_table
/


Advertisements

Remove Special Charachters \n\r CR LF from data PL / SQL Oracle and .Net

In ORACLE

SELECT REPLACE(REPLACE(YourColumn,CHAR(13),NULL),CHAR(10),NULL)
FROM YourTable


FOR ONE CHAR :
SELECT replace(YourColumn,CHAR(13),'')
FROM YourTable

OR
update YourTable
set YourColumn =replace(YourColumn,CHAR(13),'')

IN .NET 
MyString.Replace("\r\n","")   

or..

MyString.Replace("\r\n",@"\n")

Potential Blacklist
Special control chars 0-31 by Unicode must be handled
White list 
[^a-zA-Zא-ת0-9._] -and punctuation chars by your choice
- this is a start of a regex pattern - [^] -everything that not in the pattern is not allowed.
also can do 
[^\w\s\p{IsHebrew}\p{P}] - not taking accesnt chars like ~^ , not finished , but a start

better handled by StringBuilder with loop through chars than Regex 
(more than 10 times better).just Whitelist of allowed chars and if in range 
like this 

if(c>=a && c<=z)...
  sb.append(c)
else
  sb.append(' '); //replacement char
 

Sql – special chars select – records with like condition including %

select * from tableName t
where t.ColumnName LIKE ‘%\%%’ escape ‘\’

Delete duplicate rows or one of the double records


Quantcast

PDRTJS_settings_285008_post_78 = { “id” : “285008”, “unique_id” : “wp-post-78”, “title” : “Delete+duplicate+rows+or+one+of+the+double+records”, “item_id” : “_post_78”, “permalink” : “http%3A%2F%2Fpandazen.wordpress.com%2F2008%2F07%2F15%2Fdelete-one-of-the-double-records%2F” } Many a time we face cases where we have to delete one of double record.

So what can we do? Here an example of it,

SQL> select * from customer;

LAST_NAME FIRST_NAME NATION
—————————————–
XHI WILLIAM INDONESIA
BRUNI CARLA FRANCE
THERON KEIRA ENGLAND
PITT DAVID USA
PITT DAVID USA

To get the double record,

SELECT last_name, first_name
FROM customer
GROUP BY last_name, first_name
HAVING count(’x’) > 1;

LAST_NAME FIRST_NAME
————————–
PITT DAVID

To delete one of the double record, (please add some filter to specify which record can be deleted)
1. standard
DELETE FROM CUSTOMER A
WHERE ROWID (
SELECT MAX(ROWID)
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
);

2. using IN
DELETE FROM CUSTOMER A
WHERE (A.last_name, A.first_name, A.nation) IN (
SELECT B.last_name, B.first_name, B.nation
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
AND A.rowid > B.rowid);

3. For the better query use “EXISTS” instead of “IN”,

DELETE FROM CUSTOMER A
WHERE EXISTS (
SELECT ‘x’
FROM customer B
WHERE A.last_name = B.last_name
AND A.first_name = B.first_name
AND A.nation = B.nation
AND A.rowid > B.rowid);

Tag Cloud

%d bloggers like this: