Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get Left-most Char of Col A to insert into Col B Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 8:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499
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
Post #1384959
Posted Wednesday, November 14, 2012 8:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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:
Cola colb
FGHIJK F



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

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1384964
Posted Wednesday, November 14, 2012 8:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499
Ah, I got it.

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

This worked.
Thanks.
Post #1384965
Posted Wednesday, November 14, 2012 8:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:03 AM
Points: 121, Visits: 442
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.
Post #1384966
Posted Wednesday, November 14, 2012 9:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:41 PM
Points: 150, Visits: 499
Thanks for the tip, Jeff. I'll look into computed columns - never used them. Yes always position 1.
Post #1384967
Posted Wednesday, November 14, 2012 10:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
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


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1384974
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse