Copy records, limit characters? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy records, limit characters?

Author

Topic

st3ady
Starting Member

2 Posts

Posted - 2006-07-20 : 16:44:52

How could I go about doing the following:

I have a column with lets say

AHA1
AHA2
AHA3
AHE1
AHE2
ARL1
ARL2

How could I make a column next to it that is basically the same but cuts off anything after the third letter? So this new column would look like:

AHA
AHA
AHA
AHE
AHE
ARL
ARL

Thanks!

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 17:13:36

select col1, left(col1,3)...

st3ady
Starting Member

2 Posts

Posted - 2006-07-20 : 17:15:53

awesome, thanks tony!

Tahsin
Starting Member

34 Posts

Posted - 2006-07-20 : 17:17:01

Since you are trying to update a column right next to what you currently have, you can use the update statement directly:

CREATE TABLE #X
(COL1 VARCHAR(4) NULL,
COL2 VARCHAR(4) NULL)
go

INSERT INTO #X (COL1) VALUES ('AHA1');
INSERT INTO #X (COL1) VALUES ('AHA2');
INSERT INTO #X (COL1) VALUES ('AHA3');
INSERT INTO #X (COL1) VALUES ('AHE1');
INSERT INTO #X (COL1) VALUES ('AHE2');
INSERT INTO #X (COL1) VALUES ('ARL1');
INSERT INTO #X (COL1) VALUES ('ARL2');
go

SELECT * FROM #X
go

UPDATE #X
SET COL2 = LEFT(COL1,3)

SELECT * FROM #X
go

DROP TABLE #X
go

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 05:51:33

quote:
Since you are trying to update a column right next to what you currently have, you can use the update statement directly:

Or you can created a computed column. Although I suspect you only wanted to add a column to your results, not to your table, so I'm pretty sure it's irrelevant...

--data
CREATE TABLE #X (COL1 VARCHAR(4) NULL)

INSERT INTO #X (COL1) VALUES ('AHA1');
INSERT INTO #X (COL1) VALUES ('AHA2');
INSERT INTO #X (COL1) VALUES ('AHA3');
INSERT INTO #X (COL1) VALUES ('AHE1');
INSERT INTO #X (COL1) VALUES ('AHE2');
INSERT INTO #X (COL1) VALUES ('ARL1');
INSERT INTO #X (COL1) VALUES ('ARL2');

--calculation
alter table #x add COL2 as LEFT(COL1, 3)
select * from #X

/*results
COL1 COL2 
---- ---- 
AHA1 AHA
AHA2 AHA
AHA3 AHA
AHE1 AHE
AHE2 AHE
ARL1 ARL
ARL2 ARL
*/

--tidy up
drop table #X

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources