fill the missing/incorrect values for the gender column

Question

Post reply

fill the missing/incorrect values for the gender column

hoseam

SSCertifiable

Points: 5533
More actions
September 1, 2013 at 11:20 pm

#276753

Hi
I have a gender column like this:
Gender
--------
F
AAA
M
null
F
M
null
Can some give me an idea how can I create and implement a strategy to fill the missing/incorrect values for the gender column

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

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

the information that you have provided is not sufficient for us to understand what you want to achieve?

can you please some more detailed information like what output you want from those sample data/....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

hoseam SSCertifiable Points: 5533 More actions · Answer 2

Hi

What I want is a function, that will clean this column, inserting either F or M inplace of AAA and the nulls.

---------

F

M*

M

M*

F

M

M*

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 3

Of course it is a choice to just add a gender value and an indicator it is a value to be double checked.

Updating the current column content isn't such a big deal, handling the consequences of your choices is !

IMHO you would be better of leaving the NULL or adding an Unknown value for that column and restrict it all by a constraint !

update yourtable

set gender = 'U' /* U = unknown or just set it to NULL so there is no doubt it is unknown */

where gender is null

or gender not in ( 'M', 'F', 'U' ) ;

/* after fixing the content, avoid future faults */

alter table yourtable

alter column gender char(1) not null default 'U';

/* set gender constraints to avoid future messed up data */

ALTER TABLE dbo.x ADD CONSTRAINT

CK_gender CHECK (gender in ('M', 'F', 'U'))

;

Keep in mind, especially the last two actions may have consequences towards your current applications !

Test it - test IT

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

hoseam (9/1/2013)
Can some give me an idea how can I create and implement a strategy to fill the missing/incorrect values for the gender column

Visit the persons and interview them to see what gender they lean towards? If needed, make genetic tests?

Or at very least get a reference database which has the missing data.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

hoseam (9/1/2013)
Hi
What I want is a function, that will clean this column, inserting either F or M inplace of AAA and the nulls.

So how do you decide which of M or F to put in?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

In Thailand (where I live) there is a third gender: Kathoey

How would you handle that?

And what of those who are "undecided?"

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sqldriver SSChampion Points: 10440 More actions · Answer 7

Good luck...

Six Genders of old Israel
In the old Kingdom of Israel (1020–931 BCE) there were six officially recognized genders:
Zachar: male
Nekeveh: female
Androgynos: both male and female
Tumtum: gender neutral/without definite gender
Aylonit: female-to-male transgender people
Saris: male-to-female transgender people (often inaccurately translated as “eunuch”)