CODEWITHSUNDEEP
mysqlencoding
ebkgne
ebkgne

Reputation: 39

mysql comparison and encoding

I have a problem with MySQL here, it's quite strange actually... it's a real headbreak, I've been trying a lot of things, sticking some COLLATE, CAST, CONVERT,... but nothing, maybe i'm not doing it the right way, but I'm really lost here.

If some pro has a tip ;) here is my problem:

SELECT col FROM tabl LIMIT 0,1

returns: helloworld01 

CASE WHEN((SELECT col FROM tabl LIMIT 0,1) = 'helloworld_01') THEN 'ok' ELSE 'ko' END

returns: ok

CASE WHEN(SUBSTR('helloworld_01',1,1) = 'h') THEN 'ok' ELSE 'ko' END

returns: ok

BUT and this is where it get strange

 CASE WHEN(SUBSTR((SELECT col FROM tabl LIMIT 0,1),1,1) = 'h') 
 THEN 'ok' ELSE 'ko' END

returns: ko

after manually checking each letter i found that the result is quite strange ... numbers and letters in correct order but each character isn't the one supposed to be ... i get something like lsuejwodhb_65 .... but do not change at each request ... and they do not matche their CHAR code either ....

Upvotes: 0

Views: 44

Answers (1)

Rahul
Rahul

Reputation: 77936

That's because in your below query it's not always guaranteed to output helloworld_01 unless you have only one row which is very rare; since you have not used any order by clause. So the data returned may be different and so your condition is not matching at all. To be sure use a order by clause. Again, without an order by using LIMIT clause makes no sense.

SELECT col FROM tabl order by col LIMIT 0,1

Try your strange query like below and see if it matches this time

CASE WHEN(SUBSTR((SELECT col FROM tabl order by col LIMIT 0,1),1,1) = 'h') 
 THEN 'ok' ELSE 'ko' END

Upvotes: 1

Related Questions