---
title: "SQL and PySpark: Efficient String Slicing Techniques"
description: "The content presents two code examples: one for ETL logic in SQL and another for string slicing manipulation using PySpark, demonstrating data processing techniques."
url: https://srinimf.com/2024/10/03/etl-sql-query-and-pyspark-string-slicing-2-top-examples/
date: 2024-10-03
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2024/10/image-3.png?fit=1024%2C768&ssl=1
categories: ["PySpark"]
tags: ["PySpark Interview questions"]
type: post
lang: en
---

# SQL and PySpark: Efficient String Slicing Techniques

Here are SQL and PySpark examples on ETL and string slicing examples. In a recent interview, these were asked.

![PySpark and SQL interview questions](https://i0.wp.com/srinimf.com/wp-content/uploads/2024/10/pexels-photo-20431479.jpeg?resize=1880%2C1253&ssl=1)*Photo by Monstera Production on Pexels.com*

## PySpark String Slicing

```
from pyspark.sql import SparkSession
from pyspark.sql.functions import udf, col
from pyspark.sql.types import StringType

# Create a Spark session
spark = SparkSession.builder.appName("StringManipulation").getOrCreate()

# Sample data
data = [("sample",), ("samiple",)]
df = spark.createDataFrame(data, ["input_string"])

# Define a Python function to split and manipulate the string
def reverse_half(s):
    length = len(s)
    if length % 2 == 0:
        # For even length, reverse the left and right halves equally
        left_half = s[:length//2][::-1]
        right_half = s[length//2:][::-1]
    else:
        # For odd length, reverse the left half (which is larger) and right half
        left_half = s[:length//2 + 1][::-1]
        right_half = s[length//2 + 1:][::-1]
    return left_half + right_half

# Register the function as a UDF
reverse_half_udf = udf(reverse_half, StringType())

# Apply the UDF to the DataFrame
result_df = df.withColumn("manipulated_string", reverse_half_udf(col("input_string")))

# Show the result
result_df.show(truncate=False)
```

Related[Left Vs Right Outer Join Which is Better](https://srinimf.com/2018/06/09/real-idea-left-outer-join-you-always-need-to-code-in-db2-read-now/)

### Explanation of the Logic

1. **Calculating `length`**:
   - For an odd-length string, the length will always be an odd number. For example, consider the string `"abcde"` which has a length of 5.
2. **Using `length//2`**:
   - `length // 2` gives the index of the middle character in the string. For `"abcde"`, `length // 2` evaluates to `2`, which corresponds to the character `"c"` (the middle character).
3. **Slicing for the Left Half**:
   - **`left_half = s[:length//2 + 1][::-1]`**:
     - Here, `length // 2 + 1` calculates to `3`. Therefore, `s[:3]` gives the first three characters, [which is](https://srinimf.com/2016/04/10/synonyms-in-db2-deprecated-the-alternative-is-alias/) `"abc"`.
     - The left half consists of the characters from the start of the string up to and including the middle character:
       - In this case:
         - Original: `"abcde"`
         - Left Half: `"abc"`
         - After reversing: `"cba"`
4. **Slicing for the Right Half**:
   - **`right_half = s[length//2 + 1:][::-1]`**:
     - Here, `length // 2 + 1` evaluates to `3`, so `s[3:]` captures all characters starting from index `3`, [which is](https://srinimf.com/2017/10/21/sql-query-with-case-logic-avoid-union-why/) `"de"`.
     - The right half consists of all characters after the middle character:
       - In this case:
         - Original: `"abcde"`
         - Right Half: `"de"`
         - After reversing: `"ed"`

Output

Related[How to Create SQL UDF Quickly](https://srinimf.com/2017/10/12/spilt-string-sql-logic-to-create-function/)

```
+------------+------------------+
|input_string|manipulated_string|
+------------+------------------+
|sample      |maselp            |
|samiple     |imaselp           |
+------------+------------------+
```

![](https://i0.wp.com/dotcompatterns.wordpress.com/wp-content/uploads/2024/01/paint-small.jpg?ssl=1)

### “The more you learn, the more you earn.”

Warren Buffett

## SQL ETL Query

```
SELECT 
    ed.emp_id, 
    ed.mob_no, 
    ed.Address, 
    'Y' AS Flag  -- Keep old records with Flag as 'Y'
FROM emp_data ed

UNION ALL

-- Selecting rows from emp_latest
SELECT 
    el.emp_id,
    el.mob_no, 
    el.Address, 
    CASE 
        WHEN ed.emp_id IS NOT NULL THEN 'N'  -- Existing entry with changes, set Flag to 'N'
        ELSE 'Y'  -- Brand new entry, set Flag to 'Y'
    END AS Flag
FROM emp_latest el
LEFT JOIN emp_data ed 
    ON el.emp_id = ed.emp_id
    AND (el.mob_no <> ed.mob_no OR el.Address <> ed.Address)  -- Check for changes
WHERE ed.emp_id IS NULL OR (el.mob_no <> ed.mob_no OR el.Address <> ed.Address);
```

Tables and Data

```
CREATE TABLE emp_data (
    emp_id INT PRIMARY KEY,       -- Employee ID, primary key
    mob_no VARCHAR(15),          -- Mobile number, can store up to 15 characters
    Address VARCHAR(255),        -- Address, can store up to 255 characters
    Flag CHAR(1)                -- Flag indicating status, single character (Y/N)
);
-- 
CREATE TABLE emp_latest (
    emp_id INT PRIMARY KEY,       -- Employee ID, primary key
    mob_no VARCHAR(15),          -- Mobile number, can store up to 15 characters
    Address VARCHAR(255)         -- Address, can store up to 255 characters
);

-- 
INSERT INTO emp_data (emp_id, mob_no, Address, Flag) VALUES
(123, '7777777', 'Bangalore', 'Y'),
(456, '8888888', 'Chennai', 'Y'),
(789, '9999999', 'Pune', 'Y'),
(124, '6666666', 'Hyderabad', 'Y'),
(333, '9199191', 'Delhi', 'Y');

-- 

INSERT INTO emp_latest (emp_id, mob_no, Address) VALUES
(789, '9999999', 'Bangalore'),
(124, '6666666', 'Hyderabad'),
(333, '9199191', 'Bangalore'),
(444, '7171717', 'Jaipur');
```

![](https://i0.wp.com/dotcompatterns.wordpress.com/wp-content/uploads/2024/01/paint-small.jpg?ssl=1)

### “Live as if you were to die tomorrow. Learn as if you were to live forever.”

Mahatma Gandhi

### Explanation of the Query:

1. **First SELECT Block**:
   - This selects all existing records from the `emp_data` table with their `Flag` set to `'Y'`.
2. **Second SELECT Block**:
   - This part selects records from the `emp_latest` table.
   - The `CASE` statement is used to determine the value of `Flag`:
     - If the employee ID exists in both tables (`ed.emp_id IS NOT NULL`), this indicates that the record is not new, and we set `Flag` to `'N'` if there are changes in `mob_no` or `Address`.
     - If the record does not exist in `emp_data` (`ed.emp_id IS NULL`), it means it’s a new entry, and we set `Flag` to `'Y'`.
3. **WHERE Clause**:
   - The condition `WHERE ed.emp_id IS NULL` captures new entries that don’t exist in `emp_data`.
   - The additional condition `(el.mob_no <> ed.mob_no OR el.Address <> ed.Address)` checks for modifications. If there’s a mismatch in either `mob_no` or `Address`, it implies that the entry in `emp_latest` is not identical to the one in `emp_data`.

Output

```
emp_id	mob_no	Address	       Flag
--      -----   ------         -----
123	7777777	Bangalore	Y
124	6666666	Hyderabad	Y
333	9199191	Delhi	        Y
456	8888888	Chennai	        Y
789	9999999	Pune	        Y
124	6666666	Hyderabad	Y
333	9199191	Bangalore	N
444	7171717	Jaipur	        Y
789	9999999	Bangalore	N
```

### Result:

This query will:

- Keep the `Flag` as `'Y'` for all existing records in `emp_data`.
- Set the `Flag` to `'N'` for modified records in `emp_latest` that match existing records in `emp_data`.
- Set the `Flag` to `'Y'` for completely new entries in `emp_latest`.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2024%2F10%2F03%2Fetl-sql-query-and-pyspark-string-slicing-2-top-examples%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2024%2F10%2Fimage-3.png%3Ffit%3D1024%252C768%26ssl%3D1&description=SQL%20and%20PySpark%3A%20Efficient%20String%20Slicing%20Techniques)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2024/10/03/etl-sql-query-and-pyspark-string-slicing-2-top-examples/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2024/10/03/etl-sql-query-and-pyspark-string-slicing-2-top-examples/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2024/10/03/etl-sql-query-and-pyspark-string-slicing-2-top-examples/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2024/10/03/etl-sql-query-and-pyspark-string-slicing-2-top-examples/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL%20and%20PySpark%3A%20Efficient%20String%20Slicing%20Techniques&body=https%3A%2F%2Fsrinimf.com%2F2024%2F10%2F03%2Fetl-sql-query-and-pyspark-string-slicing-2-top-examples%2F&share=email)
-

### Like this:

Like Loading…