Member Avatar for Azii

Hello All,

I have a table with column_name as USERNAME where USERNAME is VARCHAR2(20) and I want to display it as "USERNAME@email.com".
How to write the select query for this.

QUERY:

SELECT USERNAME AS EMAIL FROM STUDENT

Thank you.

Edited by Azii
  • 2 Contributors
  • 1 Reply
  • 197 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by urtrivedi

Dani AI

Generated

A concise expansion on 's question, and a note about 's reply.

For Oracle it is common to build the address at SELECT time rather than storing it. The string-concatenation operator || is compact and portable inside Oracle, and it avoids the nested two-argument behavior of CONCAT. Basic normalization, null-handling and simple sanitization are useful additions for real data.

A simple, normalized output (trim whitespace, force lowercase):

SELECT LOWER(TRIM(username)) || '@email.com' AS email
FROM student;

Handle NULL or empty usernames by supplying a default with NVL (or COALESCE):

SELECT LOWER(NVL(TRIM(username), 'guest')) || '@email.com' AS email
FROM student;

If usernames may contain unwanted characters or be too long, remove invalid characters and truncate the local-part before appending the domain:

SELECT LOWER(
  SUBSTR(
    REGEXP_REPLACE(TRIM(username), '[^A-Za-z0-9._%+-]', ''),
    1,
    64
  )
) || '@email.com' AS email
FROM student;

Notes and cautions: Oracle treats empty strings as NULL, so use TRIM+NVL when a fallback is required. A generated string is not the same as a validated email address; use REGEXP_LIKE to validate final addresses if needed. If the result will be stored, ensure the target column is sized to hold the full address and consider uniqueness constraints or further validation to avoid collisions.

Member Avatar for urtrivedi 
select concat(username,'@irma.ac.in') as email from student
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.