This removes all zeroes, I need the trailing zero removed

Question

This removes all zeroes, I need the trailing zero removed

kabaari

SSCrazy

Points: 2235
More actions
September 4, 2009 at 10:08 am

#210295

UPDATE tbl_Assembly_Production
SET WorkOrder = REPLACE(RTRIM(REPLACE(WorkOrder, '0', ' ')), ' ', '5')

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

I wasn't sure if the trailing 0 would actually be the last digit in the string or not. If it is then this can be simplified a lot. Just use the reverse() function.

DECLARE @test VARCHAR(50)

SET @test = '01005060699010'

SELECT REPLACE(REVERSE(STUFF(REVERSE(@test),CHARINDEX('0',REVERSE(@test)),1,'*')),'*','')

GSquared SSC Guru Points: 260824 More actions · Answer 2

If you just want to get rid of the trailing zeroes from a number, you can reverse it, cast that as an Int, cast back to varchar and re-reverse.

Is that what you're trying to do? (This thread makes it look like I'm coming into the middle of a conversation that I don't know the beginning of.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

gboyer Old Hand Points: 320 More actions · Answer 3

It looks to me as if he is trying to replace the zeroes with a space and then trim off the space and then replace the space with a 5. Is that correct?

GSquared SSC Guru Points: 260824 More actions · Answer 4

gboyer (9/4/2009)
It looks to me as if he is trying to replace the zeroes with a space and then trim off the space and then replace the space with a 5. Is that correct?

If that's the case, I'd do it like this:

declare @String varchar(100);

select @String = '12340000';

select reverse(stuff(

reverse(@String),

1,

patindex('%[1-9]%', reverse(@String))-1,

replicate('5', patindex('%[1-9]%', reverse(@String))-1)));

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kabaari SSCrazy Points: 2235 More actions · Answer 5

Thanks for replying, i'm just now getting the chance to reply. I have workorder numbers that look like this: 'M205100'. I removed the M with a

UPDATE tbl_Name

SET workOrder = REPLACE(WORKORDER, 'M', ' ')

But when I do the code in the first post, I remove all zeros and I only want to remove the trailing zero. I'll try the other samples on Tuesday.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Do you want this to read 20510 or 2051 ??

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

kabaari SSCrazy Points: 2235 More actions · Answer 7

Bob,

I want it to read 20510, it should be five characters.

WayneS SSC Guru Points: 95461 More actions · Answer 8

It sounds to me that it's not so much that you want to remove the trailing zeros as it is that you only want the first 5 characters. So, try this:

UPDATE tbl_Assembly_Production

SET WorkOrder = LEFT(WorkOrder, 5)

and, for good measure, you can combine it with the code to remove the "M":

UPDATE tbl_Assembly_Production

SET WorkOrder = LEFT(REPLACE(WorkOrder, 'M', ''), 5)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

There are any number of ways to convert M205100 to 20510. You could use LEFT() and RIGHT() nested. You could STUFF() the first and last characters to blanks. You could use SUBSTRING() from position 2 for a length = len([col]) - 2. But we want to be sure that we don't oversimplify.

Wayne's solution for example has to make several assumptions: (1) The only letter that will appear in the string is "M", (2) the "M" will appear at the front of the string, and (3) only the leftmost 5 numbers are desired. Are those assumptions correct? Giving only a single example really isn't enough. Will the answer ever have to deal with any strings like these?

M20510 -- only one trailing zero

M2051000 -- three trailing zeroes

M20051 -- no trailing zeroes

M2051 -- shorter numeric string, no trailing zeroes

M200005100 -- longer numeric string

M2000051 -- longer numeric string, no trailing zeroes

X205100 -- starts with 'X', or any other letter thank 'M'

LM205100 -- starts with two letters, not just one

1205100 -- starts with a number, not a letter

If so, what should the result be in each case?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

kabaari SSCrazy Points: 2235 More actions · Answer 10

Wayne and Bob,

Thanks. I'm going to try that Tuesday.

kabaari SSCrazy Points: 2235 More actions · Answer 11

WayneS,

Thanks a lot. I used the second statement and I got the results I wanted.

Thanks again.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 12

Thanks for posting back your final solution, kabaari.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills