Get Left-most Char of Col A to insert into Col B

Question

Post reply

Get Left-most Char of Col A to insert into Col B

dbaforever

Hall of Fame

Points: 3740
More actions
November 14, 2012 at 8:32 pm

#270904

Hi experts,
For example: Col A Varchar(6) contains FGHIJK
Col B char(1)
I need to insert the first character - F in this case, into Column B
I need update a column in all rows by retrieving the left-most character of Col A and insert that value into Column B
How can this be done without getting the "would be truncated" error?
Thanks, Bill

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Is this what you need ? Look up the LEFT function in BOL Books On Line

CREATE TABLE #T(Cola VARCHAR(6),Colb CHAR(1))

INSERT INTO #T(Cola,colb)

SELECT 'FGHIJK',LEFT('FGHIJK',1)

-- check result

SELECT Cola,colb FROM #T

Result:

Colacolb

FGHIJKF

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Ah, I got it.

Update Table1

Set ColB = (Select Substring(ColA, 1, 1));

This worked.

Thanks.

JeffRush SSC Eights! Points: 855 More actions · Answer 3

If it is always going to the first character of column a, might consider redefining column b as a computed column to save you the pain of ongoing updates, etc. depending on your needs of course.

Just a thought.

dbaforever Hall of Fame Points: 3740 More actions · Answer 4

Thanks for the tip, Jeff. I'll look into computed columns - never used them. Yes always position 1.

vinu512 SSCoach Points: 15729 More actions · Answer 5

That is a very good tip from Jeff.....Computed Columns are not used a lot....they are even sometimes avoided in the cases they could be used....it may be due to negligence or ignorance.....the following links would give you a very good insight at Computed Columns :

COMPUTED COLUMNS

COMPUTED COLUMNS - Performance[/url]

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉