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

Update a Column

Good Day:

I am using SQL Server 2000. I have a table named Purchases and a varchar (8) column named Number.  I would like to remove the prefix P00 from the Number column.  For instance P0013250 should be 13250.  I also have records that do not contain the prefix P00.  How can I do this?

Thank You,
Denise
0
DeniseGoodheart
Asked:
DeniseGoodheart
  • 4
  • 3
6 Solutions
 
Chris LuttrellSenior Database ArchitectCommented:
Update yourTable
set [Number] = substring([Number], 4, len([Number]) )
where [Number] like 'P00%'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
or this way

Update tablename
set number = RIGHT(Number, 5 )
where number lile 'P00%'
0
 
Chris LuttrellSenior Database ArchitectCommented:
but what if the number is not always 5 digits after the P00?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Aneesh RetnakaranDatabase AdministratorCommented:
Good question, but thats a varchar(8) column, and there can be at a maximum of 5 characters after 'P00'
0
 
Chris LuttrellSenior Database ArchitectCommented:
ah, so it is, but it could be less than 5, say "P001234", and then you would get "01234" which may or may not be a problem, but if it was really short like "P001" it whould.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
good catch Chris

Update tablename
set number = RIGHT(Number, len(Number)- 3  )
where number lile 'P00%'
0
 
DeniseGoodheartAuthor Commented:
Thanks genius Aneeshattingal and wizard CGLuttrell.

0
 
Chris LuttrellSenior Database ArchitectCommented:
You're Welcome.  And thanks for letting us know you appreciate it.
Chris
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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