You are a T-SQL developer who needs to...

Question

Post reply

You are a T-SQL developer who needs to...

Steve Jones - SSC Editor

SSC Guru

Points: 728159
More actions
May 13, 2008 at 4:51 pm

#63494

Comments posted to this topic are about the item You are a T-SQL developer who needs to...

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I'm sure they're looking for an answer on this one, but my answer would be "None of the above"...

1. All of the updates will cause some improper change to properly formatted SSN's.

2. This type of formatting should be done in the GUI, not in the database.

3. If no GUI is present for (2) above, then the formatting should be done in the SELECT... but never in the database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

I agree with Jeff - but, just to have some fun - I would not use the 'correct' answer for this one. I would use this:

UPDATE Employee

SET SocialSecNumber = stuff(stuff(SocialSecNumber , 6, 0, '-'), 4, 0, '-');

And I probably would do that just to be different 😀

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Me too... except I'd add one thing...

UPDATE Employee

SET SocialSecNumber = stuff(stuff(SocialSecNumber , 6, 0, '-'), 4, 0, '-');

WHERE SocialSecNumber NOT LIKE '%[^0-9]%'

... just to be on the safe side.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 4

Jeffrey Williams

SSC Guru

Points: 89773

May 14, 2008 at 12:56 pm

#815496

Of course - and probably additional checking to validate that we have 9 characters, etc...

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 728159

May 14, 2008 at 1:05 pm

#815500

The QOD can't cover every condition. It's designed in this case to see if you understand SUBSTRING rather than picking the best way to do things.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Heh... no problem, Steve... I know about the QOD's and the fact that, like you said, they can't cover every caveat. It's one of those questions that someone asked me on an interview test once... and I gave them the same answer as a penciled in answer after I select the "correct" answer... couldn't resist posting similar here. 😀

The problem with testing in general is that you have to understand that you have to pick the "most" correct answer out of possibly 4 or 5 correct answers and it's not "your" most correct answer... it has to be the answer the test writer thought was most correct. I hate those types of questions... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions