select dbo.IsAlphaNumeric('ABC 123') --returns 1
select dbo.IsAlphaNumeric('ABC*123') --returns 0
Jon McGhee,
2012-01-25 (first published: 2011-11-09)
select dbo.IsAlphaNumeric('ABC 123') --returns 1
select dbo.IsAlphaNumeric('ABC*123') --returns 0
CREATE FUNCTION dbo.IsAlphaNumeric (@input varchar(100)) RETURNS bit AS BEGIN declare @i int, @max int, @c varchar(1), @asc int declare @isAN bit set @max = LEN(@input) set @isAN= 1 set @i = 0 while @i < @max begin set @i = @i + 1 set @c = SUBSTRING(@input,@i,1) set @asc = ascii(@c) set @isAN = case when @asc between 48 and 57 then 1 -- 0 9 when @asc between 65 and 90 then 1 -- A Z when @asc between 97 and 122 then 1 -- a z when @asc = 32 then 1 --space else 0 end if @isAN = 0 begin return @isAN --not alpha end end return @isAN -- is alpha END
How to change your "UPPER" or "lower" case strings to a mixed case
Learn how you can split a delimited string in a single query using XML with Divya Agrawal.
If a column is normalized, but the user really wants to see the values as a short comma separated list, how can I write a query that produces the list? Concatenating the values in a column would be pretty easy if SQL Server had a concatenate aggregate function, which it doesn't. What's more, for efficiency sake it's important to write the reporting queries without using cursors.
2009-02-09
2,240 reads
It is a simple routine that we all need to use occasionally; parsing a delimited list of strings in TSQL. In a perfect relational world, it isn't necessary, but real-world data often comes in a form that requires one of the surprising variety of routines that Anith Sen describes, along with sage advice about their use.
2008-09-24
5,751 reads
This artcle by new author Manie Verster examines the use of the PATINDEX function in T-SQL.