right('fe' + Gender,10 - len(Gender))

Thats very nice Leszek Gniadkowski.

Replace('fe'+gender,'fefe','')

@Eric - smart ;p

right('fe' + Gender,10 - len(Gender))

Oh, be careful. That's quite nice but the code isn't "bullet proof" because 1) it's dependent on the assigned length of the variable and 2) because it doesn't take into account that someone could have trailing spaces evn in a VARCHAR(). Please run the following code for what I mean.

USE tempdb
 GO
drop table SimpleTable
 CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
 GO
 INSERT INTO SimpleTable (ID, Gender)
 SELECT 1, 'female    '
 UNION ALL
 SELECT 2, 'male '
 UNION ALL
 SELECT 3, 'male    '
 GO
 SELECT *
 FROM SimpleTable

 SELECT *,right('fe' + Gender,10 - len(Gender))
   FROM SimpleTable

Note : Using a CASE expression will be simpler and almost work in all RDBMSs without any code change

Exactly! It would also NOT be dependent on column length or the presence of leading/trailing blanks. It's also obvious and easy to read code. It may even be faster although we'd have to test it to make sure.

I'm a little surprised at all the string squishing.

This makes more sense to me:

SELECT
   Gender,
   CASE Gender WHEN 'male' THEN 'female' WHEN 'female' THEN 'male' ELSE Gender END Changed
FROM SimpleTable

Or this:

SELECT S.ID, S.Gender, X.Changed
FROM
   SimpleTable S
   INNER JOIN (
      VALUES ('male', 'female'), ('female', 'male')
   ) X (Gender, Changed) ON S.Gender = X.old

Erik,

As its a puzzle, pinal was asking for the solution other than using "CASE" statement.

This is excellent and I personally loved all the solution! Great!

This comment is waiting for moderation.

commented on Jun 8 2012 1:14PM . Report Abuse This post is not formatted correctly

It's 2:25 am the next day an this still hasn't been moderated. It's again quite funny that the original post went straight through and that a simple edit needs to be moderated never mind more than 13 hours going by. You can certainly do what you want with the site but it's a part of the reason I don't hang around here much... you just can't try to make an intelligent response when more than 13 hours for an edit goes by.

@Jeff,

Apologies for the delay occurred to your reply. We had recently introduced a spam control logic (as explained here) and as per that, once a user is marked verified he/she does not need moderation anymore. It appears that we had a glitch somewhere that caused the moderation for your posts.

I checked the database and see that you are marked as a 'globally' verified user, which means that you do not need moderation. However, I am not sure whether this was previously enabled (in that case - we certainly have a bug) or it enabled after your last post.

We will be keeping a watch to see if this occurs again. If you see this happening anymore, just let us know (webmaster at beyondrelational.com) and we can step in and debug it again. Please test it by posting a test comment to this thread and it should show up right away :-)

The idea of the moderation is not to restrict real users, but only spammers. We do get a lot of posts from people advertising about various products/services on the comments and they are usually totally un-related to the topic being discussed. That was what originally prompted us to set up this process. But apparently it also adds difficulty to real users.

We are open for ideas to improve the spam control logic (please read this thread and share your thoughts) to make it less disturbing to the real users. In addition, any of you reading this thread can send an email to webmaster at beyondrelational.com and we can manually review it and flag you as 'verified' so that your future comments will not need moderation.

We really want to apologize to Jeff, because he is one of those great mentors that we respect most and who have been very keen on helping people and mentoring them.