How to select only rows where column contains alpha characters in spark?

I have a spark table where somehow text is being entered into a date field. I need to be able to select all the affect rows via regex, but am struggling to format the sql for it.

The date should look like this:

2023-02-04T07:01:02Z

so technically a 'Z' is allowed. I was hoping I could do something like this:

  select * from db.table
  where  date like '%[a-zA-Y]%'

But that clearly doesn't work.

You have some options here; let's assume you have this dataset:

+------------------------+
|my_dates                |
+------------------------+
|ASD2023-02-04T07:01:02Z |
|2023-02-04T07:01:02Z    |
|2023-02-04T07:01:02ZTEST|
|TEST                    |
|20A23-B02-04CT07:01:02Z |
+------------------------+

If you want to tolerate some values (as in your example, where you are using %), you can use:

select * from table where my_dates rlike '\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}Z'

which returns:

+------------------------+
|my_dates                |
+------------------------+
|ASD2023-02-04T07:01:02Z |
|2023-02-04T07:01:02Z    |
|2023-02-04T07:01:02ZTEST|
+------------------------+

If you want to do a strict check, you can do a full regex match (checking the beginning and ending of your string):

select * from table where my_dates rlike '^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}Z%'

which returns:

+--------------------+
|my_dates            |
+--------------------+
|2023-02-04T07:01:02Z|
+--------------------+

If your goal is simply to filter out the values which are not a valid time, you could even use:

select * From table where timestamp(my_dates) is not null

which returns:

+--------------------+
|my_dates            |
+--------------------+
|2023-02-04T07:01:02Z|
+--------------------+

Hope this helps, good luck!