Clever Geek Handbook

SQL to remove duplicate records, ignoring non-letters and numbers

I am trying to create a SQL query that only leaves "yahoo finance" and deletes the rest in the next dataset. This dataset is part of the InnoDB table.

yahoo/finance

yahoo.finance

yahoo. finance

yahoo-finance

yahoo+finance

yahoo finance

yahoo .finance

yahoo . finance

yahoo - finance

yahoo + finance

I tried to write a SQL function that removes duplicates while ignoring these characters: /.++-. However, I have failed and I appreciate any help. Thank!

+3


source to share


3 answers


Try the following:



SELECT DISTINCT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(columnA, '/', ' '), '.', ' '), ',', ' '), '+', ' '), '-', ' '), '  ', ' '), '  ', ' ')
FROM tableA;
+1


source


You might want to use LIKE

with the reg-ex pattern.

Perhaps the where clause is something like:



... WHERE Field LIKE 'yahoo[. -+]finance'

http://msdn.microsoft.com/en-us/library/ms179859.aspx

+1


source


Try this code,

SELECT * FROM table WHERE column REGEXP '^[A-Za-z ]+$'

The above code will only match alphabets and space.

And to delete other entries, try the following code,

DELETE FROM table WHERE column NOT REGEXP '^[A-Za-z ]+$';
+1


source






More articles:


All Articles