Searching on first character with CHARINDEX

Have you ever wanted to build an alphanumeric search allowing the user to click an individual letter or number range and then display the matching results? There are numerous ways to do this in multiple languages, but one way I found that works great is to just use CHARINDEX in T-SQL.

	DECLARE @AlphaNumChars varchar(50)
	SET @AlphaNumChars = 'a,1,m,s'
	SELECT *
	FROM INFORMATION_SCHEMA.TABLES
	WHERE CHARINDEX(LEFT(TABLE_NAME,1),@AlphaNumChars) > 0

view raw tsql_FindFirstCharacterWithCharIndex hosted with ❤ by GitHub

This query will return any record whose "table_name" begins with 'a', '1', 'm', or 's'. Obviously to match just on 'm', you would set @AlphaNumChars to 'm'. You could turn this into a stored procedure or keep it as an inline query. What other methods do you use? Let us know by leaving a comment.