October 29, 2008 at 9:14 am
Hello
I have this value in a number of rows within a table, P406600; and i will like to delete the P character before the numerical value
P406600 will become 406600
How do i do that?
October 29, 2008 at 9:36 am
If all you want to do is remove the P character from the columns, then this will work
UPDATE SomeTable
SET SomeColumn = REPLACE(SomeColumn,'P','')
WHERE SomeColumn LIKE 'P%'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2008 at 9:53 am
GilaMonster (10/29/2008)
If all you want to do is remove the P character from the columns, then this will work
UPDATE SomeTableSET SomeColumn = REPLACE(SomeColumn,'P','')
WHERE SomeColumn LIKE 'P%'
Just in case the OP dosen't want to remove the P character from all rows and just wants to remove the P character from that explicit value:
UPDATE SomeTable
SET SomeColumn = REPLACE(SomeColumn,'P','')
WHERE SomeColumn = 'P406600'
I know I'm being pedantic but I'd hate to see the OP update any rows that begin with a P character but might yet be valid. 🙂
October 29, 2008 at 10:03 am
Can the letter be in any position of the string or will it always be the first? If it can be in any position you won't want to use REPLACE.
October 29, 2008 at 10:18 am
there are about 25,000 rows with the P character in front of the value, and what i want to do is to remove the P, in front of all values starting with it
eg
P1233 will become 1233
P2589 will become 2589
P7895 will become 7895
P6548 will become 6548
P9635 will become 9635
October 29, 2008 at 10:23 am
In that case Gail's suggestion will work for you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply