Member Avatar for wowawiwa

Hi All,

First of all, thanks for taking time!

The problem i've got is the following:

I am trying to do an update to a range of data in a field:
I've got some products stored in database like this

l-aa223221
l-aa2243232fe32
l-aa224322ddew23

I would like to transforms these multiple rows like this:

l-aa223 should be updated or replaced like this l-223 so the aa part should be replaced. How can i achieve this?

Thanks,

ww.

P.S. It is not possible to use asterisk's for matching purpose in query, i guess.. like this;

UPDATE tabel1 SET field1='l-*' WHERE field1 = LIKE 'l-aa' something.

  • 3 Contributors
  • 3 Replies
  • 99 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mail2saion

Dani AI

Generated

Existing replies point in the right direction but need tightening and safety checks.

wants to remove the "aa" that appears immediately after "l-". suggested a global replace (risky if "aa" appears elsewhere). correctly narrowed it to the "-aa" pattern. SQL LIKE uses % (not *) to match any string, so filter rows with WHERE field1 LIKE 'l-aa%' before changing data.

A robust way is to preview the change with a SELECT that removes exactly the two characters after the hyphen, then run an UPDATE once verified. Example preview (no REPLACE shown here so it differs from earlier posts):

SELECT field1,
       STUFF(field1, CHARINDEX('-aa', field1) + 1, 2, '') AS new_value
FROM table1
WHERE field1 LIKE 'l-aa%';

After checking results, run the UPDATE inside a transaction so you can rollback if something looks wrong:

BEGIN TRAN;
UPDATE table1
SET field1 = STUFF(field1, CHARINDEX('-aa', field1) + 1, 2, '')
WHERE field1 LIKE 'l-aa%';
-- verify changes, then COMMIT or ROLLBACK

Notes and cautions:

  • Ensure CHARINDEX('-aa', field1) > 0 (the WHERE above covers that). STUFF requires a valid start position.
  • Collation/case: if codes may be upper/lower mixed, normalize or use a case-insensitive collation when matching.
  • Backup or test on a copy. For large tables, do the update in batches to avoid blowing the transaction log.
  • If the goal is instead to extract a fixed subpart (for example always keep only the first three digits after the hyphen), use SUBSTRING-based projection in the preview step to confirm exact behavior before updating.

For function reference see STUFF and LIKE in SQL Server: STUFF docs and .

Recommended Answers

All 3 Replies

Member Avatar for mail2saion 
UPDATE tabel1 SET field1=REPLACE(field1,'-aa','-')
Member Avatar for saurav.prasad28

hi wowawiwa

do this this is helful for u

Update Table1 set field1 = REPLACE(field1, 'aa', '');

Regards

Saurav

Member Avatar for mail2saion

HI,
I USE -aa IN REPLACE METHOD FOR MORE SPECIFIC CAUSE aa CAN APPEAR MORE RATHER THAN -aa

SO KEEP IN MIND THAT MY ABOVE POST WILL REPLACE ALL -aa OCCURENCE.

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.