Altering Column Values

Question

Post reply

Altering Column Values

nuhassan786

Mr or Mrs. 500

Points: 524
More actions
April 19, 2008 at 9:40 am

#206055

Hi,
I have one column with values something like this "A1704", "A2734", "CC404" and so on. I want to copy these values to another column with the extension ".html", the resulting column will look something like this "A1704.html", "A2734.html", "CC404.html"
Thanks for any help.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

For starters, start with something like Select val + '.html' from mytable

I'll leave it as an exercise to the reader to figure out how to UPDATE a column.

nuhassan786 Mr or Mrs. 500 Points: 524 More actions · Answer 2

Thanks Jeremy for your tip. I already did by using Update, but the problem was that in resulting value it adds space between two values something like this "ABC .html". Then I use LTRIM(RTRIM(@string)) which solved my problem.

Thanks

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 3

Thanks for the feedback. However we could have helped you more if you actually stated what your real problem was...

🙂

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

It would appear that the value for the "val" column is incorrect thoughout the table. It should not have trailing spaces. I'd recommend finding the root cause for that and fixing it as well as doing a cleanup on the table to repair existing problems.

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

nuhassan786 Mr or Mrs. 500 Points: 524 More actions · Answer 5

Yes Jeff you are right. I am having this problem in all of my tables. All the values have extra spaces on the right. You can easily see this by selecting the column values in Management Studio. When you select any value it give blue strip bigger then the physical value.

I will check if I found any solution for this.

Thanks anyway.

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 6

is your data type char or varchar? If the string you are holding in a char type column is less than the number defined, it will automatically be padded with spaces. varchar will not.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 7

Are the columns defined as CHAR() instead of VARCHAR()? Depending on system settings, that could be the problem.

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

john.arnott SSChampion Points: 11882 More actions · Answer 8

I wonder about why one would want to append a constant ".html" to a particular column and store the result in the database. For any larger number of rows, it may be more effiecient to allow the application to see xxxxx.html by using a view that takes care of the concatenation.

DAVNovak Hall of Fame Points: 3120 More actions · Answer 9

john.arnott (4/21/2008)
I wonder about why one would want to append a constant ".html" to a particular column and store the result in the database. For any larger number of rows, it may be more effiecient to allow the application to see xxxxx.html by using a view that takes care of the concatenation.

John,

You are correct in one way. If this field is only used in only a couple of spots in the application, SP's, or jobs, then I would agree with you that it would be better to assume the '.html' to all results from this field after trimming the value.

However, if you use this field in many multiple locations, that would mean you would have to add the code to annex '.html' cleanly while trimming the value from the field each time. That could get tedious after a while. To get around that, you would annex the '.html' in the field so that you only have to do it once.

Now there is one exception to this. In OO programing, you could have your class take care of this for you. You would just have to remember to apply this class to this field each time you use it.

There may be a few other explanations for one way or the other, but these are the ones that come to my mind quickly.

Dave Novak

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 10

Calculated column would take care of it nicely...

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

nuhassan786 Mr or Mrs. 500 Points: 524 More actions · Answer 11

Hi everyone,

Back to my problem. I am using VARCHAR() as my data type. Non of the column values holf CHAR().

Thanks

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 12

Then, you need to trace the source to the root cause.

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

Hans Lindgren SSChampion Points: 10496 More actions · Answer 13

Jeff Moden (4/20/2008)
Are the columns defined as CHAR() instead of VARCHAR()? Depending on system settings, that could be the problem.

I think Jeff might have been talking about the SET ANSI_PADDING statement

BOL 2005:

ANSI_PADDING = ON --> Trailing blanks in character values inserted into varchar columns are not trimmed. Trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column

ANSI_PADDING = OFF --> Trailing blanks in character values inserted into a varchar column are trimmed. Trailing zeros in binary values inserted into a varbinary column are trimmed.

This might mean that your ANSI_PADDING was on and your application sent extra blanks in the SQL.

Or that it was CHAR and someone decided to modify the column to VARCHAR...

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 14

Zactly...

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