SQL Server - IS*ALPHA*()

Asked By laurenquantrel on 13-Feb-09 06:08 PM
I have a string '5B3' and I want to determine if it is both not
numeric and not alpha.

For example:

ISNUMERIC('5B3') = 0

This does not help me identify that is is not all alpha either.
What I am  looking for is the equivalant of

IS*ALPHA*('5B3') = 0
or
IS*MIXED*('5B3') = 1

This string could vary in length and the position of the Alphas and
Numerics also varies.

While I am at it, is there a way to identify an specific alphanumeric
patten in a string?

For example:
strPattern = #A#A##  (where # = number, A = alpha)
strData = '9B3Z21' = true
strData = 'B93Z21' = false

Any help is appreciated.
lq




laurenquantrel replied to laurenquantrel on 14-Feb-09 10:27 AM
Answering my own question: How to determine if a string is mixed
AlphaNumeric, I wrote this SQL below. Perhaps there is a better way?

DECLARE @strTEST 	varchar(25)
DECLARE @intLength	int
DECLARE @intValue                 tinyint

SELECT @strTEST =3D 	'12a3b5'
SELECT @intLength =3D 	LEN(@strTEST)
SELECT @intValue =3D 	0

WHILE @intLength > 0

BEGIN

SELECT @intValue =3D 	CASE
WHEN ISNUMERIC(SUBSTRING(@strTEST, @intLength, 1)) =3D 1 THEN
@intValue + 1
ELSE
@intValue + 0
END

SELECT @intLength =3D  @intLength - 1

END

SELECT

intRetValue =3D 	CASE
WHEN  @intValue =3D 0 THEN
-- string is all alphas
0
WHEN @intValue/LEN(@strTEST)=3D 1 THEN
-- string is all numerics
1
ELSE
-- string is mixed alphanumeric
2
END
Plamen Ratchev replied to laurenquantrel on 14-Feb-09 10:45 AM
The following example will give you a few ideas:

CREATE TABLE Foo (
fookey INT PRIMARY KEY,
foodata VARCHAR(30));

INSERT INTO Foo VALUES(1, '5B3');
INSERT INTO Foo VALUES(2, '513');
INSERT INTO Foo VALUES(3, 'ABC');

SELECT fookey, foodata,
CASE WHEN foodata NOT LIKE '%[^0-9]%'
THEN 'numeric'
WHEN foodata NOT LIKE '%[^a-zA-Z]%'
THEN 'alpha'
ELSE 'mixed'
END AS footype,
CASE WHEN foodata LIKE '[0-9][A-Z][0-9]'
THEN 'pattern #A# match'
ELSE 'no match'
END AS pattern_match
FROM Foo;

/*

Results:

fookey      foodata   footype pattern_match
----------- --------- ------- -----------------
1           5B3       mixed   pattern #A# match
2           513       numeric no match
3           ABC       alpha   no match

*/

--
Plamen Ratchev
http://www.SQLStudio.com
Erland Sommarskog replied to laurenquantrel on 14-Feb-09 06:16 PM
(laurenquantrell@gmail.com) writes:

Certainly looks like candidate for using the CLR.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
laurenquantrel replied to Plamen Ratchev on 15-Feb-09 01:38 PM
Thanks much, certainly much better than what I was doing with my
solution.
--CELKO-- replied to laurenquantrel on 15-Feb-09 02:19 PM
So it is only special characters.  Try this:

(CASE WHEN UPPER(x) = x THEN 0 ELSE 1 END -- no alphas
+
CASE WHEN REPLACE( ..  (REPLACE x, '0', '') .. )) = x -- no digits
THEN 0 ELSE 1 END) = 0

If that is not what you meant, you can play with the predicates to get
the desired results.
Hugo Kornelis replied to --CELKO-- on 15-Feb-09 05:12 PM
This only determines that there are no LOWERCASE alphas. Could still be
some uppercase alphas...

Or the column could have a case insensitive collation and the predicate
would always be true... :(


What a convoluted and inconventient way to test for no digits. I will
prefer to use CASE WHEN x NOT LIKE '%[0-9]%'.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
--CELKO-- replied to Hugo Kornelis on 16-Feb-09 12:26 PM
CASE WHEN UPPER(x) = LOWER( x)  THEN 0 ELSE 1 END

would get around the mixed upper and lowercase problem, but not a case
insensitive collation.

The nested calls to REPLACE( ) might look convoluted and inconvenient
but the work is done on the stack while the x NOT LIKE '%[0-9]%'
predicate has to built a small finite state automata.  The nested
calls can be short-circuit evaluated (tho I doubt the SQL Server is
that smart; this is not ICON or a string language) while the NOT LIKE
cannot.
Hugo Kornelis replied to --CELKO-- on 18-Feb-09 05:51 PM
(snip)

Hi Joe,



I have always learned that in a declarative language, we specify what we
want, not how we want it to be done. Maybe the current version of the
query engine will work as you described. Maybe the next service pack
changes the. I do not want  to have to change my code after every service
pack, so I will stick to writing declarative code without worrying how SQL
Server implements this.


Since you brought it up, I decided to test anyway. On SQL Server 2005, I
used both versions of the "all numeric" test against a table with over
120,000 rows. The NOT LIKE version consistently ran in less than 0.13
seconds on my compter. The nested replace took just as consistently over
1.7 seconds. That's a factor 13!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
--CELKO-- replied to Hugo Kornelis on 19-Feb-09 08:16 AM
Okay, now we know.  I will give it a try with TRANSLATE() when I get to
Oracle or DB2 to see if it does better than a SIMILAR TO.  What will
be fun is to try it with F#.