December 1, 2011 at 12:09 am
Comments posted to this topic are about the item IsAlphaNumeric
December 1, 2011 at 12:37 am
Wonderful - but where is the script?
December 1, 2011 at 5:58 am
Whoops...no script.
December 1, 2011 at 6:29 am
My bad. I've resubmitted it. The contribution editor is... confusing. In my defense the field for the sql text strips the white space and looks odd as it became one line of code. I've tried with explorer 9 and chrome 15. I originally put the code in the "Scripts Manager", at least now I know.
December 1, 2011 at 8:35 am
But it's still not there Jon ! :w00t:
December 1, 2011 at 8:41 am
Aha! I found where you hid it eventually 😀
There's no need for a loop! What's wrong with PATINDEX for this sort of thing?
SELECT PATINDEX('%[^A-Za-z0-9 ]%', 'ABC 123')
SELECT PATINDEX('%[^A-Za-z0-9 ]%', 'ABC*123')
December 2, 2011 at 1:11 am
I expected function like that. Is string numeric or not.
CREATE FUNCTION [dbo].[IsAlphaNumeric](
@input varchar(100))
RETURNS bit
AS
BEGIN
DECLARE @i int, @max-2 int, @C varchar(1)
DECLARE @isAN bit
SET @max-2 = LEN(@input)
SET @isAN= 1
SET @i = 0
WHILE @i < @max-2 BEGIN
SET @i = @i + 1
SET @C = SUBSTRING(@input,@i,1)
IF PATINDEX('%[0-9]%', @C) = 0 BEGIN
SET @isAN = 0
BREAK
END
END
RETURN @isAN
END
December 2, 2011 at 2:00 am
dUros (12/2/2011)
I expected function like that. Is string numeric or not.
There's no need to use a while loop, it's unnecessary!
You can find non-matches in a string in one line with PATINDEX using the ^ to invert the search. See http://msdn.microsoft.com/en-us/library/ms188342.aspx
SELECT PATINDEX('%[^0-9]%', '123')
SELECT PATINDEX('%[^0-9]%', '123a')
December 2, 2011 at 5:57 am
You are true Nigel, thanks :-).
Here is now simple function:
ALTER FUNCTION [dbo].[IsAlphaNumeric](
@input varchar(100))
RETURNS bit
AS
BEGIN
DECLARE @isAN bit
SET @isAN= 0--non numeric
IF PATINDEX('%[^0-9]%', @input) = 0 BEGIN
SET @isAN = 1--numeric
END
RETURN @isAN
END
Or just test the returns of PATINDEX, ">0" or "=0" .
December 2, 2011 at 6:32 am
Agreed. My only issue with PATINDEX is it's hard to read the code, but it is leaner code.
December 3, 2011 at 10:52 pm
I have been doing this operation with the LIKE operator in the past and have never thought of the PATINDEX operator. Both ways work well, i really can't find a performance difference between the two. Here are my two final functions. Thanks for putting this out there!
CREATE FUNCTION dbo.IsAlphaNumeric2(
@input VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
DECLARE @result BIT = 1 -- default result to true
IF (@input LIKE '%[^a-Z,0-9,'' '']%')
BEGIN
SET @result = 0 -- found a non-alphanumeric character
END
RETURN @result -- return result
END
CREATE FUNCTION dbo.IsAlphaNumeric3(
@input VARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
DECLARE @result BIT = 1 -- default result to true
IF (PATINDEX('%[^a-Z,0-9,'' '']%', @input) > 0)
BEGIN
SET @result = 0 -- found a non-alphanumeric character
END
RETURN @result -- return result
END
-Eric
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply