How to check if a string contains letters only in SQL?

Question

Aryan Kumar · Answer

Sure, there are a few ways to check if strings contain letters only in SQL. Here are some examples:

Using the REGEXP_LIKE() function:

SQL

SELECT
  string,
  REGEXP_LIKE(string, '[a-zA-Z]+') AS is_letters_only
FROM
  my_table;

The REGEXP_LIKE() function takes two arguments: the string that you want to test and the regular expression that you want to use to test the string. The regular expression in this example matches any string that consists of only letters.

If the string matches the regular expression, the REGEXP_LIKE() function returns TRUE. Otherwise, it returns FALSE.

Using the CHARINDEX() function:

SQL

SELECT
  string,
  CHARINDEX('[^a-zA-Z]', string) AS is_letters_only
FROM
  my_table;

The CHARINDEX() function takes two arguments: the character that you want to find and the string that you want to search. The CHARINDEX() function returns the position of the character in the string, or 0 if the character is not found.

In this example, the CHARINDEX() function is used to find the first non-letter character in the string. If the CHARINDEX() function returns 0, then the string only contains letters. Otherwise, the string contains at least one non-letter character.

Using the LOWER() function:

SQL

SELECT
  string,
  LOWER(string) = LOWER(REPLACE(string, '[^a-zA-Z]', '')) AS is_letters_only
FROM
  my_table;

The LOWER() function converts all the characters in a string to lowercase. The REPLACE() function replaces all the non-letter characters in a string with empty strings.

In this example, the LOWER() function is used to convert the string to lowercase. The REPLACE() function is then used to replace all the non-letter characters in the string with empty strings. If the resulting string is the same as the original string, then the string only contains letters. Otherwise, the string contains at least one non-letter character.

forum

How to check if a string contains letters only in SQL?

Can you answer this question?

1 Answers