HELP WITH QUERY

Question

Post reply

HELP WITH QUERY

JROCK

Grasshopper

Points: 11
More actions
January 27, 2009 at 3:39 pm

#205887

I need help with an update query. Ultimately i need to add 0 or 00 to each field where the minimum number of characters is not met. SSN are 9 digits and if a SSN has only 7 digits in the table then we need to update the field to add a 0. Below is an example..
IDSSN
3992348909
25087968744
10029987609
988870987624

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Could you show us what you have done so far to resovle this problem yourself? It may be a simple fix to your code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Assumption, SSN is stored as an int. This may or may not be correct, but I had to start somewhere.

select

ID,

right('00' + cast(SSN as varchar(9)), 9) as SSN

from

dbo.MyDataTable

I decided not to wait.

JROCK Grasshopper Points: 11 More actions · Answer 3

Thanks Lynn for the quick response. I was thinking that we could do an update query to select all records that was less than 9 characters long. Where all SSN are less than 9 characters then a 0 needs to be added. I need to update the table name Customer.

Does that help?

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 4

JROCK (1/27/2009)
Thanks Lynn for the quick response. I was thinking that we could do an update query to select all records that was less than 9 characters long. Where all SSN are less than 9 characters then a 0 needs to be added. I need to update the table name Customer.
Does that help?

A little... the question remains, though... what is the datatype of the original column?

And since you're new to the forum, take a look at the link in my signature below. It'll help you get better answers faster on this or any forum.

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