Member Avatar for jovillanuev

Guys,

How to make an scripts if itemnumber have no '-U' then i will place this on the right side while
if there's an exising '-U' then no replacement to be done.

ItemNumber
------------------
P300-4410-DROIDERIS
P300-4110-TOUR
P333-4410-EVO-U
P333-4129-8530PUR-U
P333-4170-9330GRY-U
P333-4110-8350-U

RESULTS:
ItemNumber
------------------
P300-4410-DROIDERIS-U
P300-4110-TOUR-U
P333-4410-EVO-U
P333-4129-8530PUR-U
P333-4170-9330GRY-U
P333-4110-8350-U

Thank you

JOV

  • 2 Contributors
  • 5 Replies
  • 127 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by jbisono

Dani AI

Generated

's idea is correct for detecting a trailing -U. For a production-safe approach, trim trailing spaces, handle NULLs, and account for case-sensitive collations before applying changes. First preview what would change, then perform an update inside a transaction.

-- Preview (no data change)
SELECT ItemNumber,
       CASE WHEN RIGHT(RTRIM(ItemNumber),2) = '-U'
            THEN ItemNumber
            ELSE CONCAT(RTRIM(ItemNumber), '-U')
       END AS PreviewItemNumber
FROM dbo.YourTable;
-- Safe update: only touch non-NULL rows that don't already end with '-U'
BEGIN TRAN;

UPDATE dbo.YourTable
SET ItemNumber = CONCAT(RTRIM(ItemNumber), '-U')
WHERE ItemNumber IS NOT NULL
  AND RIGHT(RTRIM(ItemNumber),2) <> '-U';

-- Check results, then COMMIT or ROLLBACK
COMMIT;

Notes:

  • If the database uses a case-sensitive collation, compare RIGHT(UPPER(RTRIM(ItemNumber)),2) <> '-U' or use an explicit COLLATE.
  • On SQL Server versions older than 2012 (no CONCAT), use RTRIM(ItemNumber) + '-U' but guard against NULLs with ISNULL.
  • For very large tables, perform updates in batches (TOP/N loop) to avoid long locks and large transactions.
  • If suffix checks are frequent and performance matters, consider a persisted computed column for the suffix and index it.
  • Always test on a copy or run the preview SELECT first.

Recommended Answers

All 5 Replies

Member Avatar for jbisono

If you dont want to filter out just the items that does not have a -U then you can do this. I'm assuming that the '-U' is always at the end.

SELECT CASE WHEN SUBSTRING(ItemNumber, LEN(ItemNumber) - 1, 2) = '-U' THEN ItemNumber ELSE ItemNumber + '-U' END AS MyNewItemNumber
From YOURTABLE
Member Avatar for jovillanuev

Thank you very much for your reply.

Member Avatar for jovillanuev

Thank again. Its working now.

Regards,

Jov

Member Avatar for jovillanuev

Thank Again. Its Working.

Regards,

Jov

Member Avatar for jbisono

Don't mention it!!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.