Menu
I have a table in PostgreSQL and I need to detect if a column contains special characters like #,$,^,&,*,@,! etc. or is empty.
For example the table might be like this
How to write a query like this?
871
SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%\ /%'; Sometimes these ticks are very useful for searching special characters in a database.
You construct a pattern by combining literal values with wildcard characters and use the LIKE or NOT LIKE operator to find the matches. PostgreSQL provides you with two wildcards: Percent sign ( % ) matches any sequence of zero or more characters. Underscore sign ( _ ) matches any single character.
Using function regexp_replace() Using regexp_replace we can remove the special characters from the string or columns.
To list down all tables columns on a specific table in the a PostgreSQL database using psql command-line, you can use \dS your_table_name.
Finally i got the solution
select * from table where column1 ~* '[^a-z0-9]' or column2 ~* '[^a-z0-9]' or column3 ~* '[^a-z0-9]'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With