Substitute 0 and 1 values in 1 column

  • Hello everyone,

    Hi have this column named Sex and it only has value 0 and 1.

    I would like to substitute those values, 1 for female and 0 for male.

    Could you let me know how to do it in SQL?

    Thank you all in advance

    Pedro

  • You would use a case statement to do this.

    CASE sex WHEN 1 THEN 'Female'

    WHEN 0 THEN 'Male'

    END AS sex
  • So you are flipping the ones to zeros and vice versa?

    DECLARE @SomeTable TABLE
    (
    SomeId INT IDENTITY(1, 1) NOT NULL
    ,Sex TINYINT NOT NULL
    );

    INSERT @SomeTable
    (
    Sex
    )
    VALUES
    (1 )
    ,(1)
    ,(0)
    ,(1)
    ,(0);

    SELECT *
    FROM @SomeTable st;

    UPDATE @SomeTable
    SET Sex = IIF(Sex = 0, 1, 0);

    SELECT *
    FROM @SomeTable st;

     

    --Edit: looks like I misunderstood!

    • This reply was modified 1 month, 1 week ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Cool!!

    Thank you so much guys!!!

    You've been most helpful

    And I've found another way:

    , CASE F.Sex

    WHEN 1 THEN 'Female'

    WHEN 0 THEN 'Male'

    ELSE 'Both'

    END AS Sex

    Best regards

    Pedro

  • To flip 0/1 and vice versa:

    UPDATE dbo.table_name SET column = ABS(column - 1)

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • <quote>UPDATE dbo.table_name SET column = ABS(column - 1)<quote>

    Nice touch! I have not seen this trick in a long time. Congrats and thank you 🙂

    Zidar's Theorem: The best code is no code at all...

  • If they were bits, you could use the NOT operator (~) -- i.e.,

    UPDATE dbo.table_name SET column = ~column

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply