Replace "the" in the beginning of a field? - 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 2008 Forums

Transact-SQL (2008)

Replace "the" in the beginning of a field?

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2013-10-25 : 17:32:16

Hello I'm trying to clean up our database and remove "The " from the beginning of the productname field. So far I'm doing this:


UPDATE items
SET     Productname = REPLACE(ProductName, 'the ', '')
WHERE (UPC = '12345')

That will remove "the ", but it does it from the whole field. How can I get it to just look at the beginning of the productname field?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-25 : 17:54:33

UPDATE items
SET Productname = REPLACE(ProductName, 'the ', '')
WHERE (UPC = '12345') AND Productname LIKE 'the %'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

chadmat
The Chadinator

1974 Posts

Posted - 2013-10-25 : 17:56:12

This should work:

UPDATE items
SET ProductName = REPLACE(left(ProductName, 4), 'The ', '') + right(ProductName, len(ProductName) - 4)
WHERE (UPC = '12345')

-Chad
Microsoft Certified Master SQL Server 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-25 : 18:02:27

Oh! Mine doesn't handle the case where it appears at the beginning and elsewhere. I added my WHERE clause to Chad's query:

UPDATE items
SET ProductName = REPLACE(left(ProductName, 4), 'The ', '') + right(ProductName, len(ProductName) - 4)
WHERE (UPC = '12345') AND Productname LIKE 'the %'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

chadmat
The Chadinator

1974 Posts

Posted - 2013-10-25 : 18:03:10

Tara,

I think that will still remove a 'the ' if there are multiple 'the 's in the field. ie 'The best version of the best product ever!' would be returned as
'best version of best product ever' rather than 'best version of the best product ever'

-Chad
Microsoft Certified Master SQL Server 2008

chadmat
The Chadinator

1974 Posts

Posted - 2013-10-25 : 18:03:59

Yep, you caught it :)

-Chad
Microsoft Certified Master SQL Server 2008

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-28 : 07:27:32

This has not been tested:


UPDATE items
SET Productname = CASE WHEN ProductName LIKE 'The %' THEN STUFF(ProductName, 1, 4, '') ELSE ProductName END
WHERE (UPC = '12345')

djj

taunt
Posting Yak Master

128 Posts

Posted - 2013-10-28 : 11:57:47

quote:

Originally posted by djj55

This has not been tested:


UPDATE items
SET Productname = CASE WHEN ProductName LIKE 'The %' THEN STUFF(ProductName, 1, 4, '') ELSE ProductName END
WHERE (UPC = '12345')

djj

This worked, I tested it by adding in a piece with product name like sp "the blah the test" and afterward it corrected it to "blah the test".

Thanks

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-29 : 10:23:31

Glad it worked.

djj

GhostX1
Starting Member

6 Posts

Posted - 2013-10-31 : 10:52:13

Should you really be changing the base data? I would imagine the same thing could happen in the future? Human error and all that!

Anyway the select below with prevent further need to re-run a similar update query in the future:

Declare @temp varchar(100)
set @temp='The best version of the best product ever!'

Select SUBSTRING(@temp,Charindex('the ',@temp,1)+4,LEN(@temp)+Charindex('the ',@temp,1)-4) Productname

Obviously remove the declare and set lines and replace @temp with your field name in the select.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources