Wednesday, July 25, 2012

Validate client-side with dataset property

There are many new and exciting things in HTML5, but one of the interesting shifts it has made in my programming is for client-side form validation using the new dataset property. This was also possible pre-HTML5 via the getAttribute() method.

The code below will first try to use the new dataset property. If that fails it will fallback to trying the getAttribute() method. This ensures that all browsers will behave the same regardless of their support for HTML5. And for good measure there is another fallback to a very generic message.

To take this a step further, you could use a dynamic programming language like ColdFusion to dynamically inject the message. You could even have it display a language-specific message (i.e., English, Spanish, etc.).

Thursday, July 12, 2012

t-sql get first item from list using substring and charindex

For those of us who have inherited non-normalized databases where lists are stored in a single column, it may be helpful to be able to get the first item in a list straight from your query. ColdFusion can do this by using ListFirst() once you return the query results, but I wanted to try to do the same in T-SQL. This would be useful if, for example, the first item in the list is the parent and the other items are children. The query below will help you identify the parent.

The first query which uses @InvalidList, will return 'Item1-Item2-Item3' because it does not match the list delimiter. The second query which uses @ValidList, will return 'Item1' only since it matched the list delimiter. The REPLACE is used to obviously strip the delimiter. Note the use of LTRIM and RTRIM to trim all spaces so our value is clean.

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.

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.