converting MS Sql server query to mysql query

[jamiljarrar1]

hi all
what will be the mysql equivalent of the following query as PATINDEX is not used by mysql


select * from table1 where substring(msg1, PATINDEX ('%[a-zA-Z]%',msg1),1) = 'v'

as msg1 is my column name and v could be in upper case

thanks in advance

 

[DonQuichote]

I think it would help to tell what you want to achieve. I do not know the patindex function, so I can't tell what it is supposed to do.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[jamiljarrar1]

thanks for your reply
actually i want to make the select query to get the first alphabet in the string.i have a table for voting
messages(msgid(int),message(varchar))
data in the table is as follows

msgid message
========= =======
1 v jameel
2 vjohn
3 $/vjohn
4 @*&Vjameel
5 %sjohn
============================
i want to fetch only that rows whos first alphabet is v ommitting anything(special characters,numeric values) before that message.
in my string there may be any special characters also like "%&#Vjameel"
so in this case i want to fetch the V.

hope that this will clear my question

Regards

 

[hvass]

What you need is reg expression and replace ...
not available in standard MySQL
Available as a UDF from

http://mysqludf.com/lib_mysqludf_preg/index.php#PREG_REPLACE_SECTION

Otherwise you are stuck doing something ugly like
if(ascii(substring(message,1,1)) between 65 and 122,substring(message,1),
if(ascii(substring(message,2,1)) between 65 and 122,substring(message,2),
if(ascii(substring(message,3,1)) between 65 and 122,substring(message,3),
if(ascii(substring(message,4,1)) between 65 and 122,substring(message,4),
if(ascii(substring(message,5,1)) between 65 and 122,substring(message,5),
if(ascii(substring(message,6,1)) between 65 and 122,substring(message,6),0))))))
in order to strip off leading characters

 

[DonQuichote]

OK. So in MySQL, you could say this as

SELECT * FROM table1 WHERE msg1 REGEXP '^[^A-za-z]{0,}[Vv]';

(not tested, but you get the idea: starting with zero or more characters that are no unaccented letters, followed by a V or a v. See the manual for more about the REGEXP operator.)


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)