Finding special character in a string in oracle | ASCII function Usage

 The ASCII function in Oracle returns the numeric ASCII code of the first character in a string.

---

✅ Syntax:

ASCII(char)

• char can be a single character or a string — only the first character is considered.
• The return value is a NUMBER representing the ASCII (or Unicode) value of that character.

---

🔍 Examples:

SELECT ASCII('A') FROM dual;       -- Returns 65
SELECT ASCII('Z') FROM dual;       -- Returns 90
SELECT ASCII('a') FROM dual;       -- Returns 97
SELECT ASCII('!') FROM dual;       -- Returns 33
SELECT ASCII('Oracle') FROM dual;  -- Returns 79 (O)

---

📌 Use Cases:

• To compare or order characters based on their ASCII values.
• To debug non-printable characters or encoding issues.
• Often used with CHR() (which does the reverse: ASCII code → character).

---

🔁 Inverse Function: CHR(n)

SELECT CHR(65) FROM dual;  -- Returns 'A'

---

Here are some clear examples demonstrating how the Oracle ASCII() function works, including practical use cases:

---

🔤 Basic Character to ASCII Value

SELECT ASCII('A') AS ascii_value FROM dual;
-- Result: 65

SELECT ASCII('z') AS ascii_value FROM dual;
-- Result: 122

SELECT ASCII('1') AS ascii_value FROM dual;
-- Result: 49

SELECT ASCII('@') AS ascii_value FROM dual;
-- Result: 64

---

📌 Using a String — Only the First Character is Considered

SELECT ASCII('Oracle') AS ascii_value FROM dual;
-- Result: 79  (ASCII value of 'O')

SELECT ASCII('123ABC') AS ascii_value FROM dual;
-- Result: 49 (ASCII of '1')

---

🔁 Combined with CHR() for Round-Trip Conversion

SELECT CHR(ASCII('B')) AS original_char FROM dual;
-- Result: 'B'

---

🔍 Compare Characters Based on ASCII Value

SELECT CASE
         WHEN ASCII('A') < ASCII('a') THEN 'A comes before a'
         ELSE 'a comes before A'
       END AS result
FROM dual;
-- Result: 'A comes before a'

---

🔍 Find Special Characters or Non-Printable Characters in Data

Let's say you have a table customers(name) and suspect non-standard characters:

SELECT name, ASCII(SUBSTR(name, 1, 1)) AS first_char_code
FROM customers
WHERE ASCII(SUBSTR(name, 1, 1)) < 32;

This query finds names where the first character is a non-printable ASCII character (control character).

---

To find non-printable ASCII characters in a string in Oracle, you can check each character's ASCII code and identify those that fall outside the printable ASCII range (32–126).

---

✅ Printable ASCII Range

• Printable characters: ASCII 32 to 126
• Non-printable: ASCII 0–31 and 127

---

🔍 Query to Detect Non-Printable Characters in a String

SELECT column_name
FROM your_table
WHERE REGEXP_LIKE(column_name, '[[:cntrl:]]');

• [:cntrl:] is a POSIX class for control (non-printable) characters.

---

🧪 Example:

Suppose you have a string with a hidden control character:

SELECT CASE
         WHEN REGEXP_LIKE('Hello'||CHR(7)||'World', '[[:cntrl:]]') THEN 'Non-printable character found'
         ELSE 'All characters printable'
       END AS result
FROM dual;

Result: Non-printable character found (CHR(7) = Bell character)

---

🔁 Identify Exact Position and Code

If you want to see the position and ASCII code of non-printable characters:

SELECT LEVEL AS position,
       SUBSTR('A'||CHR(10)||'B'||CHR(1)||'C', LEVEL, 1) AS char,
       ASCII(SUBSTR('A'||CHR(10)||'B'||CHR(1)||'C', LEVEL, 1)) AS ascii_val
FROM dual
CONNECT BY LEVEL <= LENGTH('A'||CHR(10)||'B'||CHR(1)||'C');

This shows:

• Each character
• Its ASCII code
• So you can easily spot any ASCII < 32 or = 127

🔁 Identify Exact Position and Code  in a Column of a table

If you want to see the position and ASCII code of non-printable characters:

SELECT LEVEL AS position,
       SUBSTR(n2, LEVEL, 1) AS character,
        ASCII(SUBSTR(n2, LEVEL, 1)) AS ascii_val
FROM abcd
WHERE (ASCII(SUBSTR(n2, LEVEL, 1)) <=31 OR ASCII(SUBSTR(n2, LEVEL, 1))=127 )
CONNECT BY LEVEL <= LENGTH(n2);

Heare Replace n2   - with your column Name

              abcd -  with your Table Name