Remove Special Characters, Tabs, New Lines, Spaces and HTML Tags in SQL

We come across situations where we need to remove HTML tags , New Lines, Tabs and Spaces from a database column. Also we need to allow/disallow some special characters.

This can be achieved through the following SQL queries->


Remove HTML tags->

SELECT REGEXP_REPLACE(memotext,'<[^>]*>','') FROM TB_CITIALERTS_MEMO;



Remove new line(\n) and tabs(\t) ->

SELECT REPLACE(REPLACE(memotext,CHR(10),''),CHR(13),'') FROM TB_CITIALERTS_MEMO;

CHR(10) = New Line
CHR(13) = TAB



Replace Multiple spaces by Single space ->

SELECT REGEXP_REPLACE(memotext,'( ){2,}', ' ') FROM TB_CITIALERTS_MEMO;

The above query replaces more than 1 space by a single space.



Allow some special characters ->

SELECT REGEXP_REPLACE(memotext,'[^0-9a-zA-Z&@~_!|#$%*;,(){}/\. []') FROM TB_CITIALERTS_MEMO;

The above query allows those special characers that are included in this list [^0-9a-zA-Z&@~_!|#$%*;,(){}/\. []'), here you can omit the ones that you dont want to show.



We can also combine everything together as shown below ->

SELECT REGEXP_REPLACE(REPLACE(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(memotext,'<[^>]*>',''),'( ){2,}', ' '),CHR(10),''),CHR(13),''),'[^0-9a-zA-Z&@~_!|#$%*;,(){}/\. []') memotext
FROM TB_CITIALERTS_MEMO;