August 21, 2008 at 12:16 pm
Hi All,
I have a field that contains a COMPANY name that I need to manipulate using string functions, but cannot figure out how to accomplish this. For example, I want to change "ABC Company, The" to "The ABC Company". I'm looking only for occurances of the string ", The" within a table, then removing it, then appending "The " to the beginning. I wanted to be able to do this in the SELECT statement like, "SELECT COMPANY, AS NEW_COMPANY FROM..." This would have to be conditional because not all company names end with ", The".
Thanks!
David
August 21, 2008 at 12:24 pm
SELECT Company, CASE WHEN Company LIKE '%, The' THEN 'The ' + REPLACE(Company,', The','') END AS NewCompany
FROM MyTable
August 21, 2008 at 12:29 pm
Alternate method (lots of different ways)
DECLARE @String AS VARCHAR(MAX)
SET @String = 'ABC Company, The'
SELECT @String [Original],
RIGHT(@String, len(@String) - charindex(', The', @string) - 1) + space(1) + LEFT(@String, charindex(', The', @string) - 1) [New],
'The ' + LEFT(@String, charindex(', The', @string) - 1) [New Alt]
So for a table adapt like follows.
SELECT Column [Original],
RIGHT(Column, len(Column) - charindex(', The', Column) - 1) + space(1) + LEFT(Column, charindex(', The', Column) - 1) [New],
'The ' + LEFT(Column, charindex(', The', Column) - 1) [New Alt]
FROM Table
WHERE RIGHT(Column, 5) = ', The'
August 21, 2008 at 12:42 pm
Wow! You guys are fast! I knew there would be multiple ways to handle, but Michael's is surprisingly simple and works beautifully. Thanks!
August 21, 2008 at 12:46 pm
Agreed.. I like his solution best. But wanted to show that there are different ways to do it depending on what you need. Good luck.
August 21, 2008 at 12:53 pm
Ahh...I spoke too soon, Michael. I had to make a minor adjustment so it worked for ALL records. I added "ELSE Company" before the END. So it now looks like:
...CASE WHEN Company LIKE '%, The' THEN 'The ' + REPLACE(Company,', The','') ELSE Company END
David
August 21, 2008 at 2:22 pm
Why not just put a test in a where clause instead of using the Case statement? That way you aren't even updating all the rows. This may be significant if your table has a dateLastUpdated column.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 21, 2008 at 2:23 pm
Doh! Just realized you are doing a simple select, not an update. My bad.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 22, 2008 at 5:36 am
It is also worth mentioning that replace function replacess ALL occurences of one string within another. So it is not always safe if you want to replace only the latest occurence. It is not likely that a company name will contain double ', The', so my digresion here may be completely irrelevant, but you may consider the following example of what might have happened if:
declare @Company varchar(50)
set @Company = 'My company, The , The'
select REPLACE(@Company,', The','') [AllReplaced], SUBSTRING(@Company, 1, len(@Company)-5) [LastReplaced]
Regards
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply