Database Headache

[jaymc]

I have the following username in my database

[code]????s???????????????[/code]

However, I cant find where it is! And it wont appear in a search such as

[code]SELECT *
FROM `members`
`username` = "????s???????????????"
LIMIT 0 , 30[/code]

I have a feeling the '?' character is confusing MYSQL.

I need to find and delete that entry, any ideas how?

[HuggieBear]

Try:

[code]SELECT * FROM `members` WHERE `username` LIKE '%?%';[/code]

See what that brings up, as far as I'm aware, the '?' isn't a wild card in MySQL, however, the '%' and '_' are.

Regards
Huggie

[jaymc]

Didnt return anything

Basically what im trying to do, is convert a field from ascii_binary to ascii_general so that it is not case sensitive

However, to acheieve that, their cant be any duplicate usernames

When I try and change it to ascii_general it tells me that their is a duplicate entry of that username with all the question marks

[code]SQL query:

ALTER TABLE `members` CHANGE `username` `username` VARCHAR( 20 ) CHARACTER SET armscii8 COLLATE armscii8_general_ci NOT NULL

MySQL said: Documentation
#1062 - Duplicate entry '????s???????????????' for key 2 [/code]

really stuck

[jaymc]

Any ideas?

[fenway]

Try WHERE username LIKE '____s%'

[jaymc]

Nope, that just returned 19 pages of 30 rows, checked through each manually and it wasnt their

This is a nightmare.

Surely their must be a more straight forward way to convert from Binary to Ascii... on a field that is unique

Is their any way to force it and delete any duplicates it finds

[fenway]

There's no guarantee it'll be unique after, especially if the collation ends up with non-printable character for the random code it encounters.

[jaymc]

Sorted !!

The question marks where not actually question marks

They where just being shown on screen as question marks because of the charset

I did this to narrow the search results

Try WHERE username LIKE '____s___________%'

And it came up fine

Now converted

Thanks guys!