Substitute 0 and 1 values in 1 column

Question

Substitute 0 and 1 values in 1 column

pedroccamara

SSC-Addicted

Points: 431
More actions
August 16, 2022 at 10:31 am

#4076886

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

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

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

Ant-Green SSC Guru Points: 113563 More actions · Answer 1

You would use a case statement to do this.

CASE sex WHEN 1 THEN 'Female'

WHEN 0 THEN 'Male'

END AS sex

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

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 3 years ago by Phil Parkin.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101200 More actions · Answer 4

To flip 0/1 and vice versa:

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Zidar SSC Enthusiast Points: 138 More actions · Answer 5

<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...

ratbak Default port Points: 1421 More actions · Answer 6

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

UPDATE dbo.table_name SET column = ~column