Happiest Minds SQL and Pyspark Interview Question

One of the Interview question recently asked in Happiest Minds interview.
We need to Get only integer values in the output.
Lets see how we can solve this by using try_cast() in SQL and Pyspark.
Create table and insert data
CREATE TABLE emp_new (employee_id VARCHAR(50) )
INSERT INTO emp_new (employee_id) VALUES ('72657'),('1234'),('Tom'),('8792'),('Sam'),('19998'),('Philip')
For more Azure Data Bricks interview questions. Check out our playlist.
• DataBricks and PySpark...
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@sravankumar1767]

Superb explanation

[@Pranav_1407]

select employee_id from emp_new where employee_id like '%[0-9]%'

[@siddharthchoudhary103]

how to create dataframe directly instead of creating table first?

[@sudhindrab1606]

data = ["72657","1234","Tom","8792","Sam","19998","Philip"]
df=spark.createDataFrame(data,StringType()).toDF("emp_id")

[@CloudChallengers]

@siddharthchoudhary103, I hope it helps.

[@siddharthchoudhary103]

@@sudhindrab1606 thanks

[@dasubabuch1596]

Hi Sir, I tried in Oracle db.
with t as
(
select employee_id, cast(regexp_replace(employee_id,'[^0-9]+','') as number)as num from emp_new
)
select employee_id from t where num is not null;
with t as
(
select '72657' as emp_id from dual
union
select '1234' as emp_id from dual
union
select 'Tom' as emp_id from dual
union
select '8792' as emp_id from dual
union
select 'Sam' as emp_id from dual
union
select '19998' as emp_id from dual
union
select 'Philip' as emp_id from dual
)
select * from t where regexp_like(emp_id,'[0-9]');

[@CloudChallengers]

@dasubabuch1596, Thanks for sharing

[@prabhatgupta6415]

select employee_id from emp_new where employee_id/employee_id!=0;

[@sudhindrab1606]

select * from emp_new
where ISNUMERIC(employee_id)=1

[@harshitsalecha221]

SELECT * FROM emp_new
WHERE employee_id NOT IN (SELECT * FROM emp_new
WHERE employee_id regexp "[A-z]");

[@ravimogha1044]

from pyspark.sql.functions import col
from pyspark.sql.types import IntegerType
e_df1=emp_df.select(col("employee_id").cast(IntegerType())).filter(col("employee_id").isNotNull())
e_df1.show()