Leer Advanced String Manipulation

When handling customer email addresses, you often need to protect user privacy by masking sensitive information. The SQL code uses the regexp_replace function to replace everything before the @ symbol in each email address with ***. This means only the domain part remains visible, while the actual username is hidden. String functions like regexp_replace are essential in SQL for transforming data and ensuring sensitive details are masked or standardized according to business requirements.


              1234
            
SELECT
  name,
  regexp_replace(email, '^[^@]+', '***') AS masked_email
FROM customers;

Cleaning up user input is another essential task in database management. Users may accidentally enter extra spaces at the beginning or end of their names or cities. The TRIM function removes both leading and trailing spaces from a string, while LTRIM removes spaces only from the start, and RTRIM removes spaces only from the end. This ensures your data is consistent and easier to work with in queries and reports.


              123456
            
-- Remove unwanted spaces from city names
SELECT
  city,
  TRIM(city) AS cleaned_city,
  LTRIM(RTRIM(city)) AS also_cleaned_city
FROM customers;

Extracting parts of a string is another common requirement. Suppose you need to split the city name into two parts, such as the first word and the rest. You can use CHARINDEX to find the position of a space and then combine it with SUBSTRING to extract each part.


              12345
            
SELECT
  city,
  substring(city from 1 for position(' ' in city || ' ') - 1) AS first_word,
  substring(city from position(' ' in city || ' ') + 1)       AS remaining
FROM customers;

1. Which function removes leading and trailing spaces from a string?

2. What does REPLACE do in a SQL query?

3. Fill in the blanks to extract the first word from the city column using SUBSTRING and CHARINDEX.

Was alles duidelijk?

Bedankt voor je feedback!

Sectie 1. Hoofdstuk 4

Vraag AI

Vraag wat u wilt of probeer een van de voorgestelde vragen om onze chat te starten.

Suggested prompts:

Can you explain how the regexp_replace function works in this context?

What other methods can I use to mask email addresses in SQL?

Can you show how to mask only part of the username instead of the whole thing?

Veeg om het menu te tonen


              1234
            
SELECT
  name,
  regexp_replace(email, '^[^@]+', '***') AS masked_email
FROM customers;