Link to home
Create AccountLog in
Avatar of holemania
holemania

asked on

SQL - Alphanumceric only

I have data in a field that's like the following:

ABC1243
2333-AR
23RDD-432
0099809
899299
23990901

I want to filter any of the ones with a letter in it.  I was able to find this alphanumeric only function, but for some reason it doesn't seem to be working properly.

CREATE FUNCTION [dbo].[ALPHANUMERICONLY]
(
@STRING VARCHAR(MAX)
)

RETURNS VARCHAR(MAX)

AS

BEGIN 
	WHILE PATINDEX('%[^a-zA-Z0-9]%', @STRING) > 0
		SET @STRING = STUFF(@STRING, PATINDEX('%[^a-zA-Z0-9]%', @STRING), 1, '')
	RETURN(@STRING)
END

Open in new window


Here's my select statement:

Select dbo.Alphanumericonly(ID) from Part_Master

Is there something wrong with the function?  Seems to be filtering out my alphanumeric instead.
ASKER CERTIFIED SOLUTION
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of holemania
holemania

ASKER

Thank you.