qod 12/29

Question

qod 12/29

Donald Sawford

SSCrazy

Points: 2778
More actions
December 29, 2003 at 9:43 am

#106964

I think that a rtrim is required inside the right() function to return correct results, such as: UPDATE Employee SET SocialSecNumber = LEFT(SocialSecNumber,3) + '-' + SUBSTRING(SocialSecNumber,4,2) + '-' + RIGHT(RTRIM(SocialSecNumber),4) .
Without this, some spaces (depending upon table structure) will probably be returned as part of the last sequence. Therefore, none of the above would seem to be the correct answer. I am still learning SQL, so please let me know if I am missing something.

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

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

jxflagg Hall of Fame Points: 3817 More actions · Answer 1

You would be correct if there are spaces in the data. Although the question didn't state it, I assumed that the field was some type of varchar and that there were no spaces in the data. There shouldn't be if the data was entered correctly.

Frankly, I'd be a bit upset if my answer was marked incorrect because I assumed that the data was clean.

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 2

If the original field was a char(xx) field, then I think you do need the rtrim, then again, if it was char(xx) we would need to check that there is enough space for the dashes. If it is varchar then we don't have to rtrim. However, in my daily life, I tend to rtrim just in case.

Michelle

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 3

I guess the two extra characters that are needed in the SocialSecNumber col. for the two hyphens must magically appear too. What if the original table/col was built with only 9 char?

Donald Sawford SSCrazy Points: 2778 More actions · Answer 4

Thanks for the input. I am learning much from this forum. I didn't consider that it might work if varchar, since most of the tables I work with use char() fields. I have on more than one occasion tested an update query similar to what is being attempted here to find that if I had run it, it would have wiped out part or all of the data because I forgot the RTRIM(). I guess that is another advantage besides smaller tables for the varchar data type.