开发者

Filter SQL Query based on Non-Alpha First Character

开发者 https://www.devze.com 2023-02-17 05:47 出处:网络
I\'m trying to filter a VARCHAR column where the first letter is NOT an alpha. Ex. values = [\'.net\', \'30 days\', \'apple\', \'beta\']

I'm trying to filter a VARCHAR column where the first letter is NOT an alpha.

Ex.

values = ['.net', '30 days', 'apple', 'beta']

returns ['.net', '30 days']

Note: for reference this is to group the names into filter buckets by first letter,开发者_如何学JAVA where anything not an alpha character is grouped into '#' (think iPhone Contacts Browse grouping).

Filtering on a single alpha is easy with LIKE or substring, but I can't find a simple way to filter for ALL non-alpha characters.

EDIT: It is case-sensitive, but I'm expecting all lower-case, all the time.


Is the solution as easy as:

SELECT * 
FROM SomeTable 
WHERE SomeColumn NOT LIKE '[A-z]%'

?

EDIT: Changed [A-Z] to [A-z] just in case you're using a case-sensitive collation.


select * from table
where ASCII(Name) NOT BETWEEN 65/* A */ and 90/* Z */
 AND ASCII(Name) NOT BETWEEN 97 /* a */ AND 122 /* z */

You didn't specify flavor so this is TSQL though I believe ASCII is supported in most SQL implementations. The inline comment characters (/* */) may differ.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号