SQL statement (MS SQL Server) that retrieves all rows (using one
column) that do not consist of only a-z, A-Z or 0-9. SO would not
return a row that had 'sdfjnjd7733NNNND' but would return
'sdcsnjn-77N' for example

Presuming you're working with normal ascii data, the easiest way to do
this is to check the field being "not like" any of the disallowed
characters.

Unfortunately the like expression doesn't allow ranges by character
code, but you can use the char() function to get a particular
character, and it seems to work OK for ranges.

For normal ascii data, the ranges to exclude are space to the
character before 0, the character after 9 to the character before A,
the character after Z to the character before a, then the character
after z to the end of the characters.

so...

select *
from MyTable
where MyField not like
'%[ -' + char(ascii('0')-1) 
+ char(ascii('9')+1) + '-' + char(ascii('A')-1) 
+ char(ascii('Z')+1) + '-' + char(127) + ']%'

I see this as being a simple 'like' query using wildcards and a not
(^) operator applied to a range.

Let's suppose that the table is myTable and has a varchar filed called myField.

The query I have in mind would be:

select * from myTable where myField like '%[^a-zA-Z0-9]%'

In other words the query requires that somewhere in column myField,
there's a character _other than_ an alphanumeric character.


regards, mathtalk-ga

Maybe the best approach would be to look at it from the other way
around. Instead of looking at the a-z, A-Z, and 0-9 you should find
all items that contain the other characters you want.

Either way, here is a SQL function you can add to your database and an
example of how you can call it to return back data.

You can copy and paste this into SQL Query Analyzer and run it to put
this function into your database:

--- START PASTE

CREATE FUNCTION
dbo.find_regex
(
	@regex varchar(255),
	@fieldname varchar(8000)
)
RETURNS int
AS
BEGIN
	declare @obj int
	declare @res int
	declare @match bit
	set @match=0
 
	exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
	IF (@res <> 0)
	BEGIN
	      RETURN NULL
	END
	exec @res=sp_OASetProperty @obj, 'Pattern', @regex
	IF (@res <> 0)
	BEGIN
	      RETURN NULL
	END
	exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1
	IF (@res <> 0)
	BEGIN
	      RETURN NULL
	END
	exec @res=sp_OAMethod @obj, 'Test',@match OUT, @fieldname
	IF (@res <> 0)
	BEGIN
	      RETURN NULL
	END
	exec @res=sp_OADestroy @obj
	return @match
END
GO

--- END PASTE


Then to use this query to get back all the rows that contain
~!@#$%^&*()_+-= in a field called "Name":

SELECT * FROM tbl_Site WHERE dbo.find_regex('\b[~!@#$%^&*()_+\-=]',Name) <> 0