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?

Fortissimo

Answer 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.

Azelea

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

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

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

@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. – Azelea

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 ]%' – Besom

Answer 2

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

Syllogistic

Answer 3

As proposed in the accepted answer, you can use the LIKE operator to check for the characters of a particular alphabet, e.g. English. There is no proper native way in SQL Server, though, to check if a given UNICODE character is a letter or digit. You can use the below code to do that. It does not work as bad as it looks.

CREATE FUNCTION [$IsLetter$](@s nchar(1))
RETURNS bit
AS BEGIN
    DECLARE @c int = UNICODE(@s)
    RETURN CASE WHEN @c>=65 AND @c<91 OR @c>=97 AND @c<123 OR @c>=170 AND @c<171 OR @c>=181 AND @c<182 OR @c>=186 AND @c<187 OR @c>=192 AND @c<215 OR @c>=216 AND @c<247 OR @c>=248 AND @c<706 OR @c>=710 AND @c<722 OR @c>=736 AND @c<741 OR @c>=748 AND @c<749 OR @c>=750 AND @c<751 OR @c>=880 AND @c<885 OR @c>=886 AND @c<888 OR @c>=890 AND @c<894 OR @c>=895 AND @c<896 OR @c>=902 AND @c<903 OR @c>=904 AND @c<907 OR @c>=908 AND @c<909 OR @c>=910 AND @c<930 OR @c>=931 AND @c<1014 OR @c>=1015 AND @c<1154 OR @c>=1162 AND @c<1328 OR @c>=1329 AND @c<1367 OR @c>=1369 AND @c<1370 OR @c>=1376 AND @c<1417 OR @c>=1488 AND @c<1515 OR @c>=1519 AND @c<1523 OR @c>=1568 AND @c<1611 OR @c>=1646 AND @c<1648 OR @c>=1649 AND @c<1748 OR @c>=1749 AND @c<1750 OR @c>=1765 AND @c<1767 OR @c>=1774 AND @c<1776 OR @c>=1786 AND @c<1789 OR @c>=1791 AND @c<1792 OR @c>=1808 AND @c<1809 OR @c>=1810 AND @c<1840 OR @c>=1869 AND @c<1958 OR @c>=1969 AND @c<1970 OR @c>=1994 AND @c<2027 OR @c>=2036 AND @c<2038 OR @c>=2042 AND @c<2043 OR @c>=2048 AND @c<2070 OR @c>=2074 AND @c<2075 OR @c>=2084 AND @c<2085 OR @c>=2088 AND @c<2089 OR @c>=2112 AND @c<2137 OR @c>=2144 AND @c<2155 OR @c>=2160 AND @c<2184 OR @c>=2185 AND @c<2191 OR @c>=2208 AND @c<2250 OR @c>=2308 AND @c<2362 OR @c>=2365 AND @c<2366 OR @c>=2384 AND @c<2385 OR @c>=2392 AND @c<2402 OR @c>=2417 AND @c<2433 OR @c>=2437 AND @c<2445 OR @c>=2447 AND @c<2449 OR @c>=2451 AND @c<2473 OR @c>=2474 AND @c<2481 OR @c>=2482 AND @c<2483 OR @c>=2486 AND @c<2490 OR @c>=2493 AND @c<2494 OR @c>=2510 AND @c<2511 OR @c>=2524 AND @c<2526 OR @c>=2527 AND @c<2530 OR @c>=2544 AND @c<2546 OR @c>=2556 AND @c<2557 OR @c>=2565 AND @c<2571 OR @c>=2575 AND @c<2577 OR @c>=2579 AND @c<2601 OR @c>=2602 AND @c<2609 OR @c>=2610 AND @c<2612 OR @c>=2613 AND @c<2615 OR @c>=2616 AND @c<2618 OR @c>=2649 AND @c<2653 OR @c>=2654 AND @c<2655 OR @c>=2674 AND @c<2677 OR @c>=2693 AND @c<2702 OR @c>=2703 AND @c<2706 OR @c>=2707 AND @c<2729 OR @c>=2730 AND @c<2737 OR @c>=2738 AND @c<2740 OR @c>=2741 AND @c<2746 OR @c>=2749 AND @c<2750 OR @c>=2768 AND @c<2769 OR @c>=2784 AND @c<2786 OR @c>=2809 AND @c<2810 OR @c>=2821 AND @c<2829 OR @c>=2831 AND @c<2833 OR @c>=2835 AND @c<2857 OR @c>=2858 AND @c<2865 OR @c>=2866 AND @c<2868 OR @c>=2869 AND @c<2874 OR @c>=2877 AND @c<2878 OR @c>=2908 AND @c<2910 OR @c>=2911 AND @c<2914 OR @c>=2929 AND @c<2930 OR @c>=2947 AND @c<2948 OR @c>=2949 AND @c<2955 OR @c>=2958 AND @c<2961 OR @c>=2962 AND @c<2966 OR @c>=2969 AND @c<2971 OR @c>=2972 AND @c<2973 OR @c>=2974 AND @c<2976 OR @c>=2979 AND @c<2981 OR @c>=2984 AND @c<2987 OR @c>=2990 AND @c<3002 OR @c>=3024 AND @c<3025 OR @c>=3077 AND @c<3085 OR @c>=3086 AND @c<3089 OR @c>=3090 AND @c<3113 OR @c>=3114 AND @c<3130 OR @c>=3133 AND @c<3134 OR @c>=3160 AND @c<3163 OR @c>=3165 AND @c<3166 OR @c>=3168 AND @c<3170 OR @c>=3200 AND @c<3201 OR @c>=3205 AND @c<3213 OR @c>=3214 AND @c<3217 OR @c>=3218 AND @c<3241 OR @c>=3242 AND @c<3252 OR @c>=3253 AND @c<3258 OR @c>=3261 AND @c<3262 OR @c>=3293 AND @c<3295 OR @c>=3296 AND @c<3298 OR @c>=3313 AND @c<3315 OR @c>=3332 AND @c<3341 OR @c>=3342 AND @c<3345 OR @c>=3346 AND @c<3387 OR @c>=3389 AND @c<3390 OR @c>=3406 AND @c<3407 OR @c>=3412 AND @c<3415 OR @c>=3423 AND @c<3426 OR @c>=3450 AND @c<3456 OR @c>=3461 AND @c<3479 OR @c>=3482 AND @c<3506 OR @c>=3507 AND @c<3516 OR @c>=3517 AND @c<3518 OR @c>=3520 AND @c<3527 OR @c>=3585 AND @c<3633 OR @c>=3634 AND @c<3636 OR @c>=3648 AND @c<3655 OR @c>=3713 AND @c<3715 OR @c>=3716 AND @c<3717 OR @c>=3718 AND @c<3723 OR @c>=3724 AND @c<3748 OR @c>=3749 AND @c<3750 OR @c>=3751 AND @c<3761 OR @c>=3762 AND @c<3764 OR @c>=3773 AND @c<3774 OR @c>=3776 AND @c<3781 OR @c>=3782 AND @c<3783 OR @c>=3804 AND @c<3808 OR @c>=3840 AND @c<3841 OR @c>=3904 AND @c<3912 OR @c>=3913 AND @c<3949 OR @c>=3976 AND @c<3981 OR @c>=4096 AND @c<4139 OR @c>=4159 AND @c<4160 OR @c>=4176 AND @c<4182 OR @c>=4186 AND @c<4190 OR @c>=4193 AND @c<4194 OR @c>=4197 AND @c<4199 OR @c>=4206 AND @c<4209 OR @c>=4213 AND @c<4226 OR @c>=4238 AND @c<4239 OR @c>=4256 AND @c<4294 OR @c>=4295 AND @c<4296 OR @c>=4301 AND @c<4302 OR @c>=4304 AND @c<4347 OR @c>=4348 AND @c<4681 OR @c>=4682 AND @c<4686 OR @c>=4688 AND @c<4695 OR @c>=4696 AND @c<4697 OR @c>=4698 AND @c<4702 OR @c>=4704 AND @c<4745 OR @c>=4746 AND @c<4750 OR @c>=4752 AND @c<4785 OR @c>=4786 AND @c<4790 OR @c>=4792 AND @c<4799 OR @c>=4800 AND @c<4801 OR @c>=4802 AND @c<4806 OR @c>=4808 AND @c<4823 OR @c>=4824 AND @c<4881 OR @c>=4882 AND @c<4886 OR @c>=4888 AND @c<4955 OR @c>=4992 AND @c<5008 OR @c>=5024 AND @c<5110 OR @c>=5112 AND @c<5118 OR @c>=5121 AND @c<5741 OR @c>=5743 AND @c<5760 OR @c>=5761 AND @c<5787 OR @c>=5792 AND @c<5867 OR @c>=5873 AND @c<5881 OR @c>=5888 AND @c<5906 OR @c>=5919 AND @c<5938 OR @c>=5952 AND @c<5970 OR @c>=5984 AND @c<5997 OR @c>=5998 AND @c<6001 OR @c>=6016 AND @c<6068 OR @c>=6103 AND @c<6104 OR @c>=6108 AND @c<6109 OR @c>=6176 AND @c<6265 OR @c>=6272 AND @c<6277 OR @c>=6279 AND @c<6313 OR @c>=6314 AND @c<6315 OR @c>=6320 AND @c<6390 OR @c>=6400 AND @c<6431 OR @c>=6480 AND @c<6510 OR @c>=6512 AND @c<6517 OR @c>=6528 AND @c<6572 OR @c>=6576 AND @c<6602 OR @c>=6656 AND @c<6679 OR @c>=6688 AND @c<6741 OR @c>=6823 AND @c<6824 OR @c>=6917 AND @c<6964 OR @c>=6981 AND @c<6989 OR @c>=7043 AND @c<7073 OR @c>=7086 AND @c<7088 OR @c>=7098 AND @c<7142 OR @c>=7168 AND @c<7204 OR @c>=7245 AND @c<7248 OR @c>=7258 AND @c<7294 OR @c>=7296 AND @c<7305 OR @c>=7312 AND @c<7355 OR @c>=7357 AND @c<7360 OR @c>=7401 AND @c<7405 OR @c>=7406 AND @c<7412 OR @c>=7413 AND @c<7415 OR @c>=7418 AND @c<7419 OR @c>=7424 AND @c<7616 OR @c>=7680 AND @c<7958 OR @c>=7960 AND @c<7966 OR @c>=7968 AND @c<8006 OR @c>=8008 AND @c<8014 OR @c>=8016 AND @c<8024 OR @c>=8025 AND @c<8026 OR @c>=8027 AND @c<8028 OR @c>=8029 AND @c<8030 OR @c>=8031 AND @c<8062 OR @c>=8064 AND @c<8117 OR @c>=8118 AND @c<8125 OR @c>=8126 AND @c<8127 OR @c>=8130 AND @c<8133 OR @c>=8134 AND @c<8141 OR @c>=8144 AND @c<8148 OR @c>=8150 AND @c<8156 OR @c>=8160 AND @c<8173 OR @c>=8178 AND @c<8181 OR @c>=8182 AND @c<8189 OR @c>=8305 AND @c<8306 OR @c>=8319 AND @c<8320 OR @c>=8336 AND @c<8349 OR @c>=8450 AND @c<8451 OR @c>=8455 AND @c<8456 OR @c>=8458 AND @c<8468 OR @c>=8469 AND @c<8470 OR @c>=8473 AND @c<8478 OR @c>=8484 AND @c<8485 OR @c>=8486 AND @c<8487 OR @c>=8488 AND @c<8489 OR @c>=8490 AND @c<8494 OR @c>=8495 AND @c<8506 OR @c>=8508 AND @c<8512 OR @c>=8517 AND @c<8522 OR @c>=8526 AND @c<8527 OR @c>=8579 AND @c<8581 OR @c>=11264 AND @c<11493 OR @c>=11499 AND @c<11503 OR @c>=11506 AND @c<11508 OR @c>=11520 AND @c<11558 OR @c>=11559 AND @c<11560 OR @c>=11565 AND @c<11566 OR @c>=11568 AND @c<11624 OR @c>=11631 AND @c<11632 OR @c>=11648 AND @c<11671 OR @c>=11680 AND @c<11687 OR @c>=11688 AND @c<11695 OR @c>=11696 AND @c<11703 OR @c>=11704 AND @c<11711 OR @c>=11712 AND @c<11719 OR @c>=11720 AND @c<11727 OR @c>=11728 AND @c<11735 OR @c>=11736 AND @c<11743 OR @c>=11823 AND @c<11824 OR @c>=12293 AND @c<12295 OR @c>=12337 AND @c<12342 OR @c>=12347 AND @c<12349 OR @c>=12353 AND @c<12439 OR @c>=12445 AND @c<12448 OR @c>=12449 AND @c<12539 OR @c>=12540 AND @c<12544 OR @c>=12549 AND @c<12592 OR @c>=12593 AND @c<12687 OR @c>=12704 AND @c<12736 OR @c>=12784 AND @c<12800 OR @c>=13312 AND @c<19904 OR @c>=19968 AND @c<42125 OR @c>=42192 AND @c<42238 OR @c>=42240 AND @c<42509 OR @c>=42512 AND @c<42528 OR @c>=42538 AND @c<42540 OR @c>=42560 AND @c<42607 OR @c>=42623 AND @c<42654 OR @c>=42656 AND @c<42726 OR @c>=42775 AND @c<42784 OR @c>=42786 AND @c<42889 OR @c>=42891 AND @c<42955 OR @c>=42960 AND @c<42962 OR @c>=42963 AND @c<42964 OR @c>=42965 AND @c<42970 OR @c>=42994 AND @c<43010 OR @c>=43011 AND @c<43014 OR @c>=43015 AND @c<43019 OR @c>=43020 AND @c<43043 OR @c>=43072 AND @c<43124 OR @c>=43138 AND @c<43188 OR @c>=43250 AND @c<43256 OR @c>=43259 AND @c<43260 OR @c>=43261 AND @c<43263 OR @c>=43274 AND @c<43302 OR @c>=43312 AND @c<43335 OR @c>=43360 AND @c<43389 OR @c>=43396 AND @c<43443 OR @c>=43471 AND @c<43472 OR @c>=43488 AND @c<43493 OR @c>=43494 AND @c<43504 OR @c>=43514 AND @c<43519 OR @c>=43520 AND @c<43561 OR @c>=43584 AND @c<43587 OR @c>=43588 AND @c<43596 OR @c>=43616 AND @c<43639 OR @c>=43642 AND @c<43643 OR @c>=43646 AND @c<43696 OR @c>=43697 AND @c<43698 OR @c>=43701 AND @c<43703 OR @c>=43705 AND @c<43710 OR @c>=43712 AND @c<43713 OR @c>=43714 AND @c<43715 OR @c>=43739 AND @c<43742 OR @c>=43744 AND @c<43755 OR @c>=43762 AND @c<43765 OR @c>=43777 AND @c<43783 OR @c>=43785 AND @c<43791 OR @c>=43793 AND @c<43799 OR @c>=43808 AND @c<43815 OR @c>=43816 AND @c<43823 OR @c>=43824 AND @c<43867 OR @c>=43868 AND @c<43882 OR @c>=43888 AND @c<44003 OR @c>=44032 AND @c<55204 OR @c>=55216 AND @c<55239 OR @c>=55243 AND @c<55292 OR @c>=63744 AND @c<64110 OR @c>=64112 AND @c<64218 OR @c>=64256 AND @c<64263 OR @c>=64275 AND @c<64280 OR @c>=64285 AND @c<64286 OR @c>=64287 AND @c<64297 OR @c>=64298 AND @c<64311 OR @c>=64312 AND @c<64317 OR @c>=64318 AND @c<64319 OR @c>=64320 AND @c<64322 OR @c>=64323 AND @c<64325 OR @c>=64326 AND @c<64434 OR @c>=64467 AND @c<64830 OR @c>=64848 AND @c<64912 OR @c>=64914 AND @c<64968 OR @c>=65008 AND @c<65020 OR @c>=65136 AND @c<65141 OR @c>=65142 AND @c<65277 OR @c>=65313 AND @c<65339 OR @c>=65345 AND @c<65371 OR @c>=65382 AND @c<65471 OR @c>=65474 AND @c<65480 OR @c>=65482 AND @c<65488 OR @c>=65490 AND @c<65496 OR @c>=65498 AND @c<65501 THEN 1 ELSE 0 END;
END

CREATE FUNCTION [$IsDigit$](@s nchar(1))
RETURNS bit
AS BEGIN
    DECLARE @c int = UNICODE(@s)
    RETURN CASE WHEN @c>=48 AND @c<58 OR @c>=1632 AND @c<1642 OR @c>=1776 AND @c<1786 OR @c>=1984 AND @c<1994 OR @c>=2406 AND @c<2416 OR @c>=2534 AND @c<2544 OR @c>=2662 AND @c<2672 OR @c>=2790 AND @c<2800 OR @c>=2918 AND @c<2928 OR @c>=3046 AND @c<3056 OR @c>=3174 AND @c<3184 OR @c>=3302 AND @c<3312 OR @c>=3430 AND @c<3440 OR @c>=3558 AND @c<3568 OR @c>=3664 AND @c<3674 OR @c>=3792 AND @c<3802 OR @c>=3872 AND @c<3882 OR @c>=4160 AND @c<4170 OR @c>=4240 AND @c<4250 OR @c>=6112 AND @c<6122 OR @c>=6160 AND @c<6170 OR @c>=6470 AND @c<6480 OR @c>=6608 AND @c<6618 OR @c>=6784 AND @c<6794 OR @c>=6800 AND @c<6810 OR @c>=6992 AND @c<7002 OR @c>=7088 AND @c<7098 OR @c>=7232 AND @c<7242 OR @c>=7248 AND @c<7258 OR @c>=42528 AND @c<42538 OR @c>=43216 AND @c<43226 OR @c>=43264 AND @c<43274 OR @c>=43472 AND @c<43482 OR @c>=43504 AND @c<43514 OR @c>=43600 AND @c<43610 OR @c>=44016 AND @c<44026 OR @c>=65296 AND @c<65306 THEN 1 ELSE 0 END;
END

Of course, this will not have brilliant performance when used to search in a table.

Cession

Answer 4

SELECT * FROM Users WHERE mail REGEXP '^[A-Za-z]';

Ingratitude

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Mead