SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Replace "the" in the beginning of a field?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taunt
Posting Yak Master

116 Posts

Posted - 10/25/2013 :  17:32:16  Show Profile  Reply with Quote
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

USA
36983 Posts

Posted - 10/25/2013 :  17:54:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/25/2013 :  17:56:12  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36983 Posts

Posted - 10/25/2013 :  18:02:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/25/2013 :  18:03:10  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/25/2013 :  18:03:59  Show Profile  Visit chadmat's Homepage  Reply with Quote
Yep, you caught it :)



-Chad
Microsoft Certified Master SQL Server 2008
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
332 Posts

Posted - 10/28/2013 :  07:27:32  Show Profile  Reply with Quote
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
Go to Top of Page

taunt
Posting Yak Master

116 Posts

Posted - 10/28/2013 :  11:57:47  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
332 Posts

Posted - 10/29/2013 :  10:23:31  Show Profile  Reply with Quote
Glad it worked.

djj
Go to Top of Page

GhostX1
Starting Member

6 Posts

Posted - 10/31/2013 :  10:52:13  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000