square brackets in empty space

Question

square brackets in empty space

Shambhu Rai 1,411

Hi Expert,

I am getting square brackets in empty spaces in databricks, i tired below but unable to remove it

[]

regexp_replace(col1, "[^a-zA-Z0-9]", "")

Krishna Bharat Yedla 66

Hello Shambhu Rai,

Thanks for the query and using Microsoft Q&A Platform.

I tried the below for your query. Please let me know if this helps.

msfthelp1

# Create a Spark session
spark = SparkSession.builder.appName("RemoveBraces").getOrCreate()
# Sample data with Braces
data = [("1", "[testdata1]"),
        ("2", "[]testdata2]"),
        ("3", "testdata3[]]")]
# Define the schema for the DataFrame
schema = ["id", "testdatacol"]
# Create a DataFrame
df = spark.createDataFrame(data, schema=schema)
df_result1 = df.select("testdatacol", f.regexp_replace(f.col("testdatacol"),"[\[\]]","").alias("replaced"))
# Show the result
df_result1.show(truncate=False)

Shambhu Rai 1,411 Reputation points

2024-01-02T19:02:41.7233333+00:00

It's blank space actually.. But showing square bracket. Tried above not working

1 answer

Your answer

Krishna Bharat Yedla 66 Reputation points

2024-01-02T16:48:29.4466667+00:00

Hello Shambhu Rai,

Thanks for the query and using Microsoft Q&A Platform.

I tried the below for your query. Please let me know if this helps.

# Create a Spark session spark = SparkSession.builder.appName("RemoveBraces").getOrCreate() # Sample data with Braces data = [("1", "[testdata1]"), ("2", "[]testdata2]"), ("3", "testdata3[]]")] # Define the schema for the DataFrame schema = ["id", "testdatacol"] # Create a DataFrame df = spark.createDataFrame(data, schema=schema) df_result1 = df.select("testdatacol", f.regexp_replace(f.col("testdatacol"),"[\[\]]","").alias("replaced")) # Show the result df_result1.show(truncate=False)
Shambhu Rai 1,411 Reputation points

2024-01-02T19:02:41.7233333+00:00

It's blank space actually.. But showing square bracket. Tried above not working

Answer 1

Amira Bedhiafi 34,336 Volunteer Moderator

If your goal is to remove only the square brackets ([]), you should adjust your regular expression to specifically target those characters.


regexp_replace(col1, "\\[|\\]", "")

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-01-09T00:51:30.8333333+00:00

Thank you, Amira Bedhiafi

Hi Shambhu Rai
I am checking to see if you had a chance to look into the above answer from Amira.

If this answers your question, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.
Shambhu Rai 1,411 Reputation points

2024-01-09T00:54:48.8433333+00:00

it is not square bracket ... it is blank space replace with boxes tried above solution but did not work
Shambhu Rai 1,411 Reputation points

2024-01-09T01:03:40.0866667+00:00

Hi Expert,

Have tried above solution but it is not square boxes ... it is blank spaces...and showing as boxes and not square boxes
Shambhu Rai 1,411 Reputation points

2024-01-11T02:38:04.1466667+00:00

Here is the rows 1st is rowheader and followed 2 rows ok_TYPE||ok1||LAST_ok2_DATE||ok7||ok5||ok6||ok7 Ip||Ap||2029-12-1:19:32:59||2023-12-20:19:32:59.571826||dffd||EfTdfdfL ||Latsssin 0000000000000000000000000000000000000000 0 00000 00 Ip||Ap||2029-12-1:19:32:59||2023-12-20:19:32:59.571826||dffd||EfTdfdfL ||Latsssin 00
Amira Bedhiafi 34,336 Reputation points Volunteer Moderator

2024-01-15T13:03:23.5733333+00:00

The regular expression [^a-zA-Z0-9] in regexp_replace(col1, "[^a-zA-Z0-9]", "") is designed to remove all characters that are not alphanumeric. This should theoretically remove the square brackets if they are part of the data.

Becareful, if the square brackets are placeholders for null or empty values, then regexp_replace might not be the right approach. Instead, you might need to directly address null or empty values in your data. Sometimes, the way data is displayed can be due to the environment settings. Check if there's a setting in Databricks that changes how null or empty values are displayed.

Share via

square brackets in empty space

1 answer

Your answer