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 letter '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 chunks 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!

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!

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.developersdex.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 letter '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.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Array in WHERE clause

Hello, how can I use an array in a WHERE clause?

If I had an array say

string[] NamesArray = new string[] {"Tom", "***", "Harry"}

How would I do this:

string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray"

Thanks in advance,

Louis

for a SQL Statement, you would need to serialize out the array of names into text (BTW that's "SELECT", not "SELET":

string myquery = "SELECT name, city, country FROM myTable WHERE name in ('george', 'jack', harry')

|||

Whatpbromberg wrote is correct, and here is another option which is also correct.

Insert you array items into a table (say MyArrayTable) and use this code:

1 SELET [name],2 city,3 country4FROM myTable5WHERE [name]in (6SELECT [Name]7 From MyArrayTable8 )9GO

Good luck.

|||

Thanks guys for your help.

It works,

Louis