finding non-ascii character records

[DotNetGnat]

Guys,

I have a table with a text field called ResumeText

Lets say simplified table looks like below...

EmpID | ResumeText
__________________
1 | blahblahblah
2 |sdklfjndsfds

what i am trying to do is to find only those records that have a non ascii characters in the ResumeText...

for example...

SELECT EmpID, ResumeText FROM mytable
WHERE PATINDEX('% %',ResumeText)>0

gives me all the records that contain a single space...

how to i write something similar for the filtering all the records that containing nonascii character...do i need to write multiple Patindexes to cover all the nonascii characters...

what is the best way to deal this situation...

-DNG

 

[SQLDenis]

you mean like
where ResumeText not like '[a-Z]'
or
where ResumeText like '[^a-Z]'
Is that what you want?

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLDenis]

Correction
where ResumeText not like '%[a-Z]%'
or
where ResumeText like '%[^a-Z]%'

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[DotNetGnat]

i ended up using...

patindex('%[^a-zA-Z0-9 ]%',ResumeText) <> 0

thanks for the idea SQLDenis.

-DNG

 

[dcdogxx]

Can this also be done to look for hex 0A 0D in a field.

I have a very similar situation. I am trying to export files to Excel, but am having problems with Carriage Returns in the text field. In Excel, they show as "text text text||" When I export this to another file, the carriage returns are throwing my records off.

Can I search in SQL for hex 0D 0A and remove them?

Thanks

 

[DotNetGnat]

yeah...just replace inside patindex with whatever you are looking for...

-DNG

 

[dcdogxx]

How do I look for a HEX 0D 0A?

Sorry, that is what I was asking.

 

[DotNetGnat]

Select blah from yourtable
Patindex('%hx(OD)%', yourfield)>0
AND
Patindex('%hx(OA)%', yourfield)>0

-DNG

 

[DotNetGnat]

oops...i missed where keyword

Select blah from yourtable
WHERE
Patindex('%hx(OD)%', yourfield)>0
AND
Patindex('%hx(OA)%', yourfield)>0

-DNG

 

[yelworcm]

Try
patindex ('%' + char(13) + '%', field)
and
patindex ('%' + char(10) + '%', field)

 

[SQLDenis]

Take a look at this

declare @string varchar(200)
select @string ='abc
a
a
a'

select @string,replace(@string,char(13) + char(10),'')

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[DotNetGnat]

i guess what ever yelworcm suggested should work for you...

just add the following...

patindex ('%' + char(13) + '%', field)>0
and
patindex ('%' + char(10) + '%', field)>0

-DNG