Remove first two characters and place in seperate field

Hi,

Is it possible to remove the first two characters from a table column and place those characters in another separate field.
The fields are both VARCHAR.
Any help would be appreciated.
Thanks
Morpheus7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Select myField, substring(myField,3,len(myField)-2) as FromCharacter3, substring(myField,1,2) as FirstTwoCharacters
from MyTable
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure:
UPDATE TableName
SET ColTwo = LEFT(ColOne,2),
   ColOne = RIGHT(ColOne,LEN(ColOne)-2),

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
ColOne is the field to remove the first two characters.
ColTwo is the field where those two characters will be placed.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Lee SavidgeCommented:
If the data has leading or trailing spaces, you may want to trim and you can avoid the rows that have no data in the field.

update mytable
set mycol2 = left(ltrim(rtrim(mycol1)), 2),
   mycol1 = right(ltrim(rtrim(mycol1)), len(ltrim(rtrim(mycol1))) - 2)
where ltrim(rtrim(isnull(mycol1, ''))) <> ''

Open in new window

0
 
Scott PletcherSenior DBACommented:
FYI, the separate column with only two characters would be more efficient as:
char(2)
rather than varchar(2).
0
 
LowfatspreadCommented:
be aware that len will result in you losing trailing spaces from the column if that could be important to you.

you might want to use datalength instead which deals with numbers of bytes (which need to be divided by 2 if it is an NVARCHAR column)
0
 
Morpheus7Author Commented:
Thanks to all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.