Hi - I want to select all rows where the string contained within a
varchar column, C1, contains a character outside of the range x20 to
x7E.

So if, for instance, a copyright symbol (xA9) appeared within the
string contained in C1 I want that row to be output.

My charset is binary.

Thanks for any ideas.

Regards

Richard.

northof40 wrote:
> Hi - I want to select all rows where the string contained within a
> varchar column, C1, contains a character outside of the range x20 to
> x7E.
>
> So if, for instance, a copyright symbol (xA9) appeared within the
> string contained in C1 I want that row to be output.
>
> My charset is binary.
>
> Thanks for any ideas.
>
> Regards
>
> Richard.
>

Have you looked at the binary operator - possibly used with the cast
operator??

On Feb 14, 4:31 pm, Michael Austin <maus...@firstdbasource.com> wrote:
> northof40 wrote:
> > Hi - I want to select all rows where the string contained within a
> > varchar column, C1, contains a character outside of the range x20 to
> > x7E.
>
> > So if, for instance, a copyright symbol (xA9) appeared within the
> > string contained in C1 I want that row to be output.
>
> > My charset is binary.
>
> > Thanks for any ideas.
>
> > Regards
>
> > Richard.
>
> Have you looked at the binary operator - possibly used with the cast
> operator??

Thanks for your reply. I have now tried out the binary operator and it
certainly does some good but I still have some questions !

This is what I've got (in the following the value of TE1_C1 in the row
with TE1_AUTOID = 6 is not really 'a' but something to do with cutting
and pasting it has made it look like an a) ...

mysql> insert into test1 values (null,bin(224));

mysql> insert into test1 values (null,'abc');

mysql> select * from test1;
+------------+--------+
| TE1_AUTOID | TE1_C1 |
+------------+--------+
| 6 | a |
| 9 | abc |
+------------+--------+
2 rows in set (0.00 sec)

mysql> select * from test1 where TE1_C1 = char(224);
+------------+--------+
| TE1_AUTOID | TE1_C1 |
+------------+--------+
| 6 | a |
+------------+--------+
1 row in set (0.00 sec)

mysql> select TE1_C1,instr(binary(TE1_C1),char(224)) from test1;
+--------+---------------------------------+
| TE1_C1 | instr(binary(TE1_C1),char(224)) |
+--------+---------------------------------+
| a | 1 |
| abc | 0 |
+--------+---------------------------------+
2 rows in set (0.00 sec)

.... so that's really great it does give me a way of finding the rows I
wanted but can anyone suggest a way to now test for all the values I'm
interested in (without an very large number of OR's !).

I think I really want to be able to say (making up my own 'between'
function here !)...

select TE1_C1,instr(binary(TE1_C1),between(char(127), char(255)) from
test1;

.... can anyone suggest a way to do that ?

thanks

Richard.