sql / intermediate
Snippet
ANSI Standard String Extraction and Position Functions
Standard ANSI SQL provides portable string functions such as SUBSTRING, POSITION, and CHARACTER_LENGTH. Using standard string operations ensures cross-platform SQL compatibility.
snippet.sql
sql
1
2
3
4
5
6
7
SELECTuser_id,UPPER(SUBSTRING(email FROM 1 FOR 3)) AS prefix,POSITION('@' IN email) AS domain_index,CHARACTER_LENGTH(username) AS name_lengthFROM usersWHERE POSITION('@' IN email) > 0;
Breakdown
1
UPPER(SUBSTRING(email FROM 1 FOR 3)) AS prefix,
Extracts the first 3 characters of the email and converts them to uppercase.
2
POSITION('@' IN email) AS domain_index,
Locates the 1-based character position of the `@` symbol inside the email string.
3
CHARACTER_LENGTH(username) AS name_length
Measures the number of characters present in the username field.
4
FROM users
Specifies the source table containing user profile information.
5
WHERE POSITION('@' IN email) > 0;
Filters out rows where the email field lacks a valid `@` separator.