Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

fill the missing/incorrect values for the gender column Expand / Collapse
Author
Message
Posted Sunday, September 1, 2013 11:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:45 AM
Points: 193, Visits: 328
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
Post #1490512
Posted Sunday, September 1, 2013 11:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:04 AM
Points: 1,972, Visits: 2,410
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/
Post #1490513
Posted Sunday, September 1, 2013 11:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:45 AM
Points: 193, Visits: 328
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*
Post #1490515
Posted Monday, September 2, 2013 1:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, December 20, 2014 8:56 AM
Points: 6,748, Visits: 8,545
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1490536
Posted Monday, September 2, 2013 3:53 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:19 PM
Points: 825, Visits: 756
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.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1490698
Posted Monday, September 2, 2013 4:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 40,667, Visits: 37,129
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 2008, MVP
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

Post #1490702
Posted Monday, September 2, 2013 7:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:40 AM
Points: 3,443, Visits: 5,404
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1490724
Posted Tuesday, September 3, 2013 11:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 495, Visits: 2,062
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”)


Post #1491055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse