How to select records with a range of ASCII character codes

Question

Post reply

How to select records with a range of ASCII character codes

Rich Ray

SSC Eights!

Points: 887
More actions
August 22, 2008 at 3:54 am

#189675

Hi,
I would like to pull back a results set with records that contain non-alphanumeric characters in a certain column. Is this possible using the ASCII codes?
Just thinking of a quicker way rather than using multiple LIKE statements.
Thanks!
Richard

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 1

Amit Khanna-455738

SSC Eights!

Points: 865

August 22, 2008 at 4:03 am

#861742

Try PATINDEX Function.

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Will this work?

select * from YourTable where YourColumn like '%[^a-zA-Z0-9]%'

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Rich Ray SSC Eights! Points: 887 More actions · Answer 3

RyanRandall (8/22/2008)
Will this work?
select * from YourTable where YourColumn like '%[^a-zA-Z0-9]%'

Thanks Ryan - That returns all alphanumeric values but I want the opposite. Tried the same as far as listing them within the square brackets but that didn't work.

Currently looking at the PATINDEX function.

RyanRandall SSChampion Points: 13623 More actions · Answer 4

richard (8/22/2008)
That returns all alphanumeric values but I want the opposite.

Huh? Did you see the not symbol (^)? Here's an example...

--Sample Data

declare @t table (v varchar(100))

insert @t

select 'H[1'

union all select 'y}1'

union all select 'a1'

union all select '1'

union all select 'g'

union all select ''

union all select null

--Query

select * from @t where v like '%[^a-zA-Z0-9]%'

/* Output

v

-----

H[1

y}1

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Rich Ray SSC Eights! Points: 887 More actions · Answer 5

Sorry, my bad, did not see the ^ symbol.

Works great now - thanks!

RyanRandall SSChampion Points: 13623 More actions · Answer 6

No worries - glad to help 🙂

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 7

Another alternative may not be the best one

SELECT

YOURCOLUMN,

CASE WHEN PATINDEX('%[~,!,@,#,$,^,&,*,(,),_,+,=,-,|,\,:,;,",?,/,.,>,<,`]%',YOURCOLUMN) !=0

THEN 'Non - Alphanumeric'

ELSE 'Alphanumeric'

END AS FLAG

FROM YOURTABLE

Thanks,

Amit Khanna

cyxop SSC Journeyman Points: 80 More actions · Answer 8

How can I add apostrophe to this expression - char(39)?

For example, I should select all alpha-numeric name. However, some names contains apostrophe, like O'Brien.

select * from YourTable where YourColumn like '%[^a-zA-Z0-9'']%'

Did not work

RyanRandall SSChampion Points: 13623 More actions · Answer 9

I should select all alpha-numeric name

This makes it sound like you have a different requirement, since the original poster was asking for non-alphanumeric data. Maybe you want something like this?

--Sample Data

declare @t table (v varchar(100))

insert @t

select 'H[1'

union all select 'y}1'

union all select 'a1'

union all select '1'

union all select 'g'

union all select ''

union all select null

union all select 'smith'

union all select 'smith1'

union all select 'smith$'

union all select 'o''brien'

--Query

select * from @t where v not like '%[^a-zA-Z'']%'

/* Output

v

-----

g

smith

o'brien

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

cyxop SSC Journeyman Points: 80 More actions · Answer 10

cyxop

SSC Journeyman

Points: 80

January 6, 2010 at 3:49 pm

#1099961

Thanks Ryan

😀