Ascii Code search

Hi;
I'm tring to create a sql query in 6.5 that will find any unprintable
characters in a text field. I'm trying to use a where clause that looks
for specific ASCII codes but cant seem to get it work. Does anyone have
any ideas how to do this.
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!David,
You can use below technique. In my example, I used CHAR(99) which is the let
ter 'c'. Replace this with the
ASCII code for the unprintable character you want to find.
SELECT *
FROM
(
SELECT 'abcdef' AS colname
UNION
SELECT 'abdef' AS colname
) AS d
WHERE CHARINDEX(CHAR(99), colname) > 0
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Cervelli" <cervelli@.adelphia.net> wrote in message news:OkpMIugiEHA.1348@.tk2msftngp13
.phx.gbl...
> Hi;
> I'm tring to create a sql query in 6.5 that will find any unprintable
> characters in a text field. I'm trying to use a where clause that looks
> for specific ASCII codes but cant seem to get it work. Does anyone have
> any ideas how to do this.
> Thanks
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Tibor
I finally tried this and it works great but I have a few issues that I
hope you might be able to help.
I'm using version 6.5 so a varchar is only 255 characters.
I'm trying to search a text field that is much larger then 255.
Your soltuion does a good job at searching a text field but not a text
field.
If I convert the text to varchar it only looks at the first 255
characters.
Do you have any suggestions?
Thanks in advance.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||If the function doesn't support the "text" datatype, then you probably have
to use functions such as
TEXTPTR, READTEXT etc to loop through your data and use the function on chun
ks of data. Not very
fun...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Cervelli" <dcervelli@.ssgnet.com> wrote in message
news:uRwc0zXkEHA.3632@.TK2MSFTNGP09.phx.gbl...
>
> Tibor
> I finally tried this and it works great but I have a few issues that I
> hope you might be able to help.
> I'm using version 6.5 so a varchar is only 255 characters.
> I'm trying to search a text field that is much larger then 255.
> Your soltuion does a good job at searching a text field but not a text
> field.
> If I convert the text to varchar it only looks at the first 255
> characters.
> Do you have any suggestions?
> Thanks in advance.
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!