March 15, 2017 at 2:38 pm
Phil Parkin - Wednesday, March 15, 2017 1:17 PMfrederico_fonseca - Wednesday, March 15, 2017 1:01 PMor another alternative to Luis code - wonder which one will run faster
UPDATE Inventory
SET ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, '')
WHERE ItemNum like '0%'
and not exists (select 1 from Inventory i2 where ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, ''))How does this avoid touching non-numeric ItemNum values?
hum...
WHERE ItemNum like '0%' - meaning that only records that start with a zero are picked
patindex('%[^0]%', ItemNum) - returns the position of the first non zero char on that field
But as I may be wrong, why do you think the above does not avoid the non-numeric values?
March 15, 2017 at 3:37 pm
frederico_fonseca - Wednesday, March 15, 2017 2:38 PMPhil Parkin - Wednesday, March 15, 2017 1:17 PMfrederico_fonseca - Wednesday, March 15, 2017 1:01 PMor another alternative to Luis code - wonder which one will run faster
UPDATE Inventory
SET ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, '')
WHERE ItemNum like '0%'
and not exists (select 1 from Inventory i2 where ItemNum = stuff(ItemNum, 1, patindex('%[^0]%', ItemNum) - 1, ''))How does this avoid touching non-numeric ItemNum values?
hum...
WHERE ItemNum like '0%' - meaning that only records that start with a zero are picked
patindex('%[^0]%', ItemNum) - returns the position of the first non zero char on that fieldBut as I may be wrong, why do you think the above does not avoid the non-numeric values?
It doesn't avoid non-numeric values. It just treat everything as a string, but only removes leading zeros.
For testing purposes:CREATE TABLE #Inventory(
ItemNum nvarchar(400),
action_desc varchar(100)
);
INSERT INTO #Inventory
VALUES
('143545043', 'All digits - No leading zeros'),
( '345230', 'All digits - No leading zeros'),
('00345230', 'All digits - Leading zeros - repeated value'),
('012234567', 'All digits - Leading zeros - unique value'),
('001542AS1', 'Alphanumeric - Leading zeros - unique value'),
('ASDF123', 'Alphanumeric - No leading zeros'),
('0000000', 'Do nothing - All zeros');
SELECT * FROM #Inventory;
UPDATE i
SET ItemNum = SUBSTRING( i.ItemNum, PATINDEX('%[^0]%', i.ItemNum), 4000)
FROM #Inventory i
WHERE i.ItemNum like '0%'
AND SUBSTRING(i.ItemNum, PATINDEX('%[^0]%',i.ItemNum), 4000) NOT IN (SELECT x.ItemNum FROM #Inventory x);
SELECT * FROM #Inventory;
GO
DROP TABLE #Inventory;
March 15, 2017 at 3:51 pm
If you look back at page 1, here is one of the requirements, as stated by the OP:
Sorry, I left out that some of the ItemNum SKU's do have letters in the ItemNum field....but I want to ignore any of those ItemNum fields...ONLY the ones that have leading zeros...
so check all ItemNum and only drop leading 0 if it begins with a leading 0
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 16, 2017 at 2:57 am
chef423 - Wednesday, March 15, 2017 12:02 PMYes, 450 rows return. Sorry, I do have some ItemNum fields that are not numbers, but I want my query to ignore those.I thought this may work, but it did not:
UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS BIGINT) AS VARCHAR(50))
WHERE LEN(ItemNum) = 12Any ideas?
Yes, I see what Phil means.
You later then say that this will generate duplicate keys if we flat do it, so you want to leave the leading zero's there if not. before you read on with my answer, why do you want to do this then? If some keys do have leading zeros, and can't not have them, because it will generate a dupliiate, why is it a problem if some do and don't have a duplicate? Why is the number 001234 permitted, but 001235 isn;t because you only have 1234 already in your table? This seems, to me, like a bad idea.
Anyway, merging but both and Phil's prior answers:UPDATE Inventory
SET ItemNum = CAST(CAST(ItemNum AS bigint) AS varchar(50))
WHERE ItemNum LIKE '0%'
AND TRY_CAST(ItemNum AS bigint) IS NOT NULL
AND TRY_CAST(ItemNum AS bigint) NOT IN (SELECT sq.ItemNum FROM Inventory sq);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 16, 2017 at 4:26 pm
Thank you to everyone that put effort here. Very much appreciated!
March 21, 2017 at 12:46 am
Please check below query.
Note: Please replace table name with your table name in select and update statements.
DECLARE @T1TBL TABLE(ItemNum VARCHAR(50))
INSERT INTO @T1TBL
SELECT '028200136107'
UNION ALL
SELECT '028200136167'
UNION ALL
SELECT '028200136177'
UNION ALL
SELECT 'TEST1'
UNION ALL
SELECT '028222136107'
UNION ALL
SELECT '027777136107'
UNION ALL
SELECT '028288136107'
UNION ALL
SELECT '028200ABCDEF'
UNION ALL
SELECT '628200233107'
UNION ALL
SELECT '728200000107'
SELECT 'Before Update' AS [Status],* FROM @T1TBL
BEGIN TRAN
UPDATE T
SET T.ItemNum = CAST(CAST(T.ItemNum AS BIGINT) AS VARCHAR(50))
FROM @T1TBL AS T
WHERE ISNUMERIC(T.ItemNum) = 1
AND T.ItemNum LIKE '0%'
--COMMIT TRAN
--ROLLBACK TRAN
SELECT 'After Update' AS [Status],* FROM @T1TBL
March 23, 2017 at 1:11 pm
chef423 - Wednesday, March 15, 2017 11:39 AMI have a data table called 'ItemNum'The column is a SKU, which is all numbers...the current format is: 028200136107
I need to remove all leading zeros (0) from the first digit, but I need SQL to leave the number alone IF it does not lead with a zero.
Something like this?
UPDATE Inventory
SET replace(ltrim(replace(ItemNum.Inventory, '0', '')), '', '0')
where ItemNum = LEN(ItemNum) = 12
Unfortunly this does not work 🙁
Thanks
This is a good example of why even the simplest narratives don't work and should be replaced with DDL. It hasn't been netiquette for 30+ years for no reason. Most of the work in SQL is actually done in the DDL and not in the DML. From your narrative, you should have declared the column to be like this:
CREATE TABLE Inventory
(sku CHAR(12) NOT NULL
CHECK (sku LIKE '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
PRIMARY KEY,
..);
It is now impossible to put a leading zero on the on the SKU. So there's no need to go back and repair the bad design on the fly. Oops! You then throw in the kicker. There some alphanumerics in the SKU. Go back and change your like predicate; make the check pattern into [0-9A-Z]. Does a final position or the lead position allow a blank?
If you really have to go back and clean up somebody's filthy mess, with what do you want to replace the leading zero? I'm assuming that it is 12 characters long; most tag numbers and identifiers are fixed length. In fact, ISO has some very strong recommendations about not allowing variable length standard identifiers.
Things like this are what we call "tag numbers" in data modeling. They are never used for math, and just happen to be strings of digits. This is because it Unicode has a standard that all alphabets must contain the digits, a basic Latin alphabet, and if you punctuation marks. Then all ISO standards use this subset for their encodings. This is for compatibility and portability in international standards.
The worst mistake people make is assuming that a tag number can be represented as an integer. Casting and recasting, trying to write math instead of a regular expression to validated, etc is just a waste of resources and time. It's also very error-prone. Quick, what's a square root of your credit card number?
Please post DDL and follow ANSI/ISO standards when asking for help.
March 23, 2017 at 2:13 pm
Maybe something like this, it removes all leading zeroes except where a value is all zeroes:
CREATE TABLE #Inventory(
ItemNum nvarchar(400),
action_desc varchar(100)
);
INSERT INTO #Inventory
VALUES
('143545043', 'All digits - No leading zeros'),
( '345230', 'All digits - No leading zeros'),
('00345230', 'All digits - Leading zeros - repeated value'),
('012234567', 'All digits - Leading zeros - unique value'),
('001542AS1', 'Alphanumeric - Leading zeros - unique value'),
('ASDF123', 'Alphanumeric - No leading zeros'),
('0000000', 'Do nothing - All zeros');
SELECT *, PATINDEX('%[^0]%',i.ItemNum) FROM #Inventory i;
UPDATE i SET
ItemNum = stuff(ItemNum,1,PATINDEX('%[^0]%',i.ItemNum),'')
FROM #Inventory i
WHERE
i.ItemNum like '0%'
AND PATINDEX('%[^0]%',i.ItemNum) > 0;
SELECT * FROM #Inventory;
GO
DROP TABLE #Inventory;
GO
March 27, 2017 at 8:55 am
Thank you everyone. This really helped me!
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply