Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Swap Values of Column - Puzzle from Pinal Dave

Jun 8 2012 12:00AM by Madhivanan   

Pinal Dave, Blogger,Speaker,Evangelist and Author,in his blog posted a puzzle about SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement

Consider the following set of data
USE tempdb
 GO
 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

There can be many solutions for this. Here are my simple ones

Method 1 : Concatenate empty string and apply replace

select gender,replace(replace (' '+gender,' fe',''),' ','fe') as new_gender from simpletable 

Method 2 : Apply multiple replaces

Solution 1
select gender,replace(replace(replace (gender,'male',' '),'fe ','male'),' ','female') as new_gender from simpletable 
 
Solution 2
select gender,replace(replace(replace (gender,'female','X'),'male','female'),'X','male') from simpletable 

In all the cases, the results are
gender     new_gender
---------- -------------------
female     male
male       female
male       female

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

Tags: tsql,sqlserver,swap


Madhivanan
3 · 40% · 12958
11
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

11  Comments  

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

    commented on Jun 8 2012 6:44AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • Thats very nice Leszek Gniadkowski.

    commented on Jun 8 2012 7:24AM
    Madhivanan
    3 · 40% · 12958
  • Replace('fe'+gender,'fefe','')

    commented on Jun 8 2012 8:20AM
    Eric Bradford
    71 · 2% · 779
  • @Eric - smart ;p

    commented on Jun 8 2012 9:02AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • 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
    
    commented on Jun 8 2012 1:12PM
    Jeff Moden
    159 · 1% · 305
  • 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.

    commented on Jun 8 2012 1:14PM
    Jeff Moden
    159 · 1% · 305
  • 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
    
    commented on Jun 8 2012 6:03PM
    ErikEckhardt
    65 · 3% · 898
  • Erik,

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

    commented on Jun 8 2012 11:28PM
    Ramireddy
    2 · 40% · 12972
  • This is excellent and I personally loved all the solution! Great!

    commented on Jun 8 2012 11:52PM
    Pinal Dave
    151 · 1% · 326
  • 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.

    commented on Jun 9 2012 1:29AM
    Jeff Moden
    159 · 1% · 305
  • @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.

    commented on Jun 9 2012 2:24AM
    webmaster
    244 · 1% · 184

Your Comment


Sign Up or Login to post a comment.

"Swap Values of Column - Puzzle from Pinal Dave" rated 5 out of 5 by 11 readers
Swap Values of Column - Puzzle from Pinal Dave , 5.0 out of 5 based on 11 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]