• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

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
0
Morpheus7
Asked:
Morpheus7
2 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now