Databricks SQL Query for finding rows having special characters and discard those rows

Question

As part of Databricks, We would like to filter rows having special characters in the columns.

Let's say we have a Table with data like:

Table1 has

Col1

Except the Last row, all rows are valid here. How do we filter just the last row here

Tried out following: select b.* from Table1 where (Col1 REGEXP '[^a-zA-Z0-9-&@.,/()#''``:$"" ]')

This is still showing all rows except 1st row.

Tried out following: select b.* from Table1 where (Col1 REGEXP '[^a-zA-Z0-9-&@.,/()#''``:$"" ]')

This is still showing all rows except 1st row.

199 Central Avenue 1664 O'block Road 577 Saint John's Road 1630 Hahn's Dairy Road --> Row 1 "N 40 Degrees 36' 15"" W -75 Degrees -27' -52""" 40*61605 North Lat-75*38'39* West Long. "40ï¿½3'13"" North — Nanda
– Nanda, Commented May 18, 2024 at 15:07

Jaya Shankar G S · Accepted Answer · 2024-05-20 05:48:10Z

0

You can use below query where you need to add you special characters that you want to avoid.

SELECT  *  FROM Table1 WHERE (Col1 not REGEXP '[+ï¿½]')

Here, i am adding whatever the characters i don't want which presents one or more times in the record .

+: For having characters one or more time.

ï¿½: Special characters i don't want.

Output:

enter image description here

answered May 20, 2024 at 5:48

Jaya Shankar G S

8,6282 gold badges6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

@Nanda Check if above provided solution works for you? Let me know if I can be helpful here anyway with further input?