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
DeniseGoodheartAsked:
Who is Participating?
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
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.