fill the missing/incorrect values for the gender

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

fill the missing/incorrect values for the gender

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-09-02 : 01:26:02

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 01:33:51

-- Sample Data
DECLARE @tab TABLE( Gender varchar(15))
insert into @tab values('F'), ('AAA'), ('M'), (null), ('F'), ('M'), (null)
-- Query
SELECT CASE WHEN Gender IN ('M', 'F') THEN 'CorrectGender' ELSE 'IncorrectGender' END As Gender, Gender
FROM @tab

--
Chandu

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-09-02 : 01:39:26

Thanks,

Is there a way we can make this a function, to clean that Gender column, and replace null values or any value that is not M or F to M or F

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-02 : 05:59:55

Use UPDATE statement
DECLARE @tab TABLE( Gender varchar(15))
insert into @tab values('F'), ('AAA'), ('M'), (null), ('F'), ('M'), (null)
-- Query
SELECT CASE WHEN Gender IN ('M', 'F') THEN 'CorrectGender' ELSE 'IncorrectGender' END As Gender, Gender
FROM @tab

UPDATE @tab
SET Gender = CASE WHEN Gender IN ('M', 'F') THEN Gender ELSE 'YourValue' END

SELECT * FROM @tab

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 09:47:47

quote:
Originally posted by stahorse

Thanks,

Is there a way we can make this a function, to clean that Gender column, and replace null values or any value that is not M or F to M or F

what will be the rule for other values? How do you determine if its a M or F for NULL and values like AAA

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources