Check if starting characters of a string are alphabetical in T-SQL

Question

Is it possible, just using TSQL, to check if the first two characters of a varchar field are alphabetical?

I need to select from my_table only the rows having my_field beginning with two alphabetical chars. How can I achieve this?

Is it possible to use a regex?

Solution 1

You don't need to use regex, LIKE is sufficient:

WHERE my_field LIKE '[a-zA-Z][a-zA-Z]%'

Assuming that by "alphabetical" you mean only latin characters, not anything classified as alphabetical in Unicode.

Note - if your collation is case sensitive, it's important to specify the range as [a-zA-Z]. [a-z] may exclude A or Z. [A-Z] may exclude a or z.

Solution 2

select * from my_table where my_field Like '[a-z][a-z]%'

Comments

[Damien_The_Unbeliever]

@Richardakacyberkiwi - incorrect. In SQL Server, for most collations, the alphabet is in the order aAbBcCdDeE...zZ. Perhaps you're thinking of ASCII?

[RichardTheKiwi]

You're right. TBH, I have never had occasion to use a case-sensitive collation.

[Saw]

This is only for first and second letter, how can I do this for all chars?

[Damien_The_Unbeliever]

@MohamedSakherSawan - Would be better searched for/asked as a separate question, but okay, I'll answer. If you want to ensure that a column contains only alphabetical characters, a double-negative search condition would work: NOT column LIKE '%[^a-Z]%' - which says to not match the column if it contains any number of characters, a character outside the set a-Z, and any number of characters.

[Saw]

I am soo sorry @Damien_The_Unbeliever, I didn't read the question, thanx very much, I used the current pattern: NOT column LIKE '%[^a-zA-Z ]%'