Truncating and copying to another column

Question

Post reply

Truncating and copying to another column

C-kweldude

Old Hand

Points: 312
More actions
April 2, 2014 at 6:42 pm

#305910

Hi Guys
I have a column called ColumnA with the following data in it
2300>MC13
What I need to achieve is copy the last five characters into ColumnB...ie
>MC13
Can someone assist with the correct statement to do this?
Thanks in advance
A

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

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

kish1234 Ten Centuries Points: 1112 More actions · Answer 1

Hi,

You can try this

ColumnB = Right(colname, LEN(colname) - 4)

Regards.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Do you always want the 5 characters on the right? Or is it always the everything to the right of and including the gt ">" symbol?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

C-kweldude Old Hand Points: 312 More actions · Answer 3

Hi

Thanks you for the reply

I just need the > symbol and the 4 characters after it

All the data is consistent in character length

A

C-kweldude Old Hand Points: 312 More actions · Answer 4

C-kweldude

Old Hand

Points: 312

April 2, 2014 at 7:05 pm

#1702693

Thank you Kish...your suggestion worked!:-)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Here is a means to get that data that allows for some variances - just in case.

DECLARE @table TABLE (ColumnA VARCHAR(20), ColumnB varchar(20))

INSERT INTO @table (ColumnA) VALUES ('2300>MC13')

SELECT SUBSTRING(ColumnA,CHARINDEX('>',ColumnA),LEN(ColumnA)-CHARINDEX('>',ColumnA)+1)

FROM @table

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Oh and here is the rest of the setup to update ColumnB

DECLARE @table TABLE (ColumnA VARCHAR(20), ColumnB varchar(20))

INSERT INTO @table (ColumnA) VALUES ('2300>MC13');

SELECT SUBSTRING(ColumnA,CHARINDEX('>',ColumnA),LEN(ColumnA)-CHARINDEX('>',ColumnA)+1)

FROM @table;

UPDATE t

SET ColumnB = SUBSTRING(ColumnA,CHARINDEX('>',ColumnA),LEN(ColumnA)-CHARINDEX('>',ColumnA)+1)

FROM @table t;

SELECT *

FROM @table;

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events