Regular expression with Spark sql

 if you have data where you want to filter out all special characters along with numeric character from string then use spark sql regular expression function  - regexp_replace


Example : -

      Name

      sbdgd&(&**,4343

      ram7534- 5%7777


Select  regexp_replace(name, '[^a-zA-z]', ' ') as name from table_name


Result:

  Name

  sbdgd

  ram

Comments

Post a Comment

Popular posts from this blog

Date format issue with spark sql

 If you have date format like  date_col 1/1/1978 0:00 6/6/1975 0:00 1/1/1978 0:00 1/1/1978 0:00 7/23/1988 0:00 1/1/1978 0:00 1/26/1961 0:00 3/3/1980 0:00 6/2/1984 0:00 1/2/1971 0:00 Solution : substring(TO_DATE(date_col ,'MM/dd/yy'),0,10) from table Result : 1978-01-01 1975-06-06 .. 1988-07-23 ..
Read more

Root path does not exist

Problem : When you execute your query you are getting below error.    Root path does not exist Root path of the dataset does not exist Solution: Please check you query it may be possibility of below cause.  Database.table in query name even you input dataset not synced with database or any metastoredb example : select * from  db1.abc Confirm if abc is table of database db. I will possible that data saved on HDFS only. In that case you need to only table e.g select * from abc 
Read more

Dataiku and Dremio date difference

In dataiku you have applied to_date function to convert string to date datatype in SparkSQL and when query same dataset in dremio then you will difference of 1 day. Example -  In dataiku      SELECT  offer_date  FROM temp      Result :      2023-05-18T00:00:00.000Z Dremio :     SELECT  offer_date  FROM temp Result :      2023-05-17 08:00:00.000 Now you will find the difference of  1 day and 8 hours (sometime 16 hours)  between Dataiku and Dremio. Solutions:       This difference is due to time difference. In Dataiku we have  correct date to correct it Dremio please use TO_TIMESTAMP function to get correct date. Example : select TO_TIMESTAMP(offer_date, 'yyyy-mm-dd' ) as offer_date from temp
Read more