Strip leading zero off certain rows only? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Strip leading zero off certain rows only?

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-29 : 09:32:27

Me again,
have a request from someone who has this problem:

quote:
Scope of Work: On every barcode that is 14 digits long, where the first digit is a zero (I don’t believe 14-digit I-2-of-5 case barcodes ever begin with a zero and I believe we have very few I2of5 case codes loaded), strip the leading zero to create a 13-digit barcode with the right most 13 digits.

Here is a SELECT statement that Rockmoose sent me for a different problem, but I think this is the way to go for this, too:

SELECT
SUBSTRING(RIGHT(BARCOD,13),PATINDEX(‘%[^0]%’,RIGHT(BARCOD,11)),13)--BARCOD FROM IM_BARCOD

I just want to doublecheck that this is written correctly for the problem at hand. There are aprox. 160,000 rows of data, of which we need to modify a select few. Is this code proper, and importantly, are the RED values correct for the problem?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-29 : 09:37:43

SELECT
RIGHT(BARCOD,13)
FROM IM_BARCOD
where len(BARCOD) = 14
and left(BARCOD,1) = '0'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-12-29 : 10:16:27

Hey nr,
yeah, I knew this would work. Question is, this data either got padded during DTS or it was corrupted when it got exported to .csv from the old app. If it got padded, then we know the only problem is the extra zero. I was concerned that during exprot to .csv there may also be spaces or some other garbage involved. I was looking for a way to display all entries in the table while stripping the known problems so that the operator could then review the output.
Make sense?

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources