SELECT iif( LIKE(sol,'EM06%'), "other",sol) as solGroup;
FROM dpgift GROUP BY solGroup
The above should give me a column containing unique sol values. In addition, any sol value starting with EM06 should be grouped into "other". It doesn't work though, instead it returns the following.
Solgroup
DM081
EM061
EM081
EM100
EM101
EM105
EM111
TM081
Can anyone see what i'm doing wrong here? the 2nd and 3rd line should be called "other".
I've used this like command many times and it's never done this before. I can't see anything wrong with the syntax and I've tried other variations without success. I've even tried casting in case a certain field type doesn't work but the result is always the same.
edit: using a '*' and putting the wildcard string as the first parameter works. I've actually been making this mistake for a while in the following manner:
iif( (LIKE("4%",sol) OR sol = "4"),"8M","other");
The like does nothing here but i hadn't noticed since the '=' operator returns true if sol starts with '4' (can someone link me a reference to '=' behavior?). The reason i included the sol = "4" is because i assumed % meant one or more char rather than 0 or more.
Anyway, i've gone back and changed all my code to iif(like("wtvr*",string),stringtrue,stringfalse)
I imagine using '=' to compare strings is not recommended since it doesn't seem to be mentioned in the msdn library.
another alternative is to use "$", AT() or ATC()
"$" means if the left side is found ANYWHERE in the right side...
iif( "EM06" $ sol, "other", sol )
AT() is to compare if a string is found in another, but IS CASE-SENSITIVE, returns the character position found in the string (VFP is 1-based, not zero-based)
iif( at( "EM06", sol ) > 0, "other", sol )
or
ATC() -- same as AT(), but is NOT case-sensitive.
If you specifically want the "EM06" to be at the start of the string, you could even just do a LEFT() comparison
iif( left( sol, 4 ) = "EM06", "other", sol )
or even
iif( ATC( "EM06", sol ) = 1, "other", sol )
and yes, these are all valid within SQL-Select..