Replace Function

[moone009]

How can I replace only "North" in this following string when it is by itself?

'E NORTHCREST DR NORTH'

UPDATE address SET streetname = REPLACE(streetname, ' NORTH',' N')

This script changes it to 'E NCREST DR N'

[BitBlt]

Tricky one. I was able to use a combination of CASE, PATINDEX and REPLACE to solve this. Here's a sample script to demonstrate. It isn't pretty, but it works:

/* First, a test table to select from... */
create table #temp
(id int identity(1, 1) not null, myString varchar(100))
/* Now some test data... */
insert into #temp
(myString)
values
('E. NorthCrest Dr. North'),
('E. NorthCrest North Dr.'),
('E. NorthCrest Dr. South'),
('E. SouthCrest Dr. North'),
('E. SouthCrest Dr. West'),
('North SouthCrest Dr. West'),
('NorthCrest Dr. East')

/* And, the statement itself... */
select *,
case patindex ('% North %', myString)
when 0 then 
	case patindex ('% North', myString)
	when 0 then 
		case patindex ('North %', myString)
		when 0 then myString
		else substring(myString, 0, patindex ('North %', myString))
		end + 
		case patindex ('North %', myString)
		when 0 then ''
		else replace (substring(myString, patindex ('North %', myString), len(myString)), 'North', 'N')
		end	
	else substring(myString, 0, patindex ('% North', myString))
	end + 
	case patindex ('% North', myString)
	when 0 then ''
	else replace (substring(myString, patindex ('% North', myString), len(myString)), 'North', 'N')
	end
else substring(myString, 0, patindex ('% North %', myString))
end + 
case patindex ('% North %', myString)
when 0 then ''
else replace (substring(myString, patindex ('% North %', myString), len(myString)), 'North', 'N')
end as myString_CleanedUp
from #temp

-- Cleanup, and we're done!
drop table #temp

And, the results:

id	myString	                  myString_CleanedUp
1	E. NorthCrest Dr. North	E. NorthCrest Dr. N
2	E. NorthCrest North Dr.	E. NorthCrest N Dr.
3	E. NorthCrest Dr. South	E. NorthCrest Dr. South
4	E. SouthCrest Dr. North	E. SouthCrest Dr. N
5	E. SouthCrest Dr. West	E. SouthCrest Dr. West
6	North SouthCrest Dr. West	N SouthCrest Dr. West
7	NorthCrest Dr. East	NorthCrest Dr. East

You can of course use a local variable in place of the literal to generalize the solution, but I'll leave that as an exercise for you.

I hope this gives you what you were looking for! Good luck!

[adam_k]

@BitBlt: That's one sick query, which like all your queries will work.

Wouldn't it be easier and less time consuming to just:

select substring(REPLACE( ' ' + myString + ' ' , ' NORTH ',' N '),2,len(myString))
from #temp

[moone009]

Thank you guys!

[BitBlt]

@adam_k, that is very clever and MUCH simpler. I had to look at it a couple of times before I "got it". Well done!

However, it does leave an extra space on the end, so perhaps using LTRIM and RTRIM instead of SUBSTRING would be better?

Like so:

select rtrim(ltrim(REPLACE( ' ' + myString + ' ' , ' NORTH ',' N ')))
from #temp

But kudos to you for seeing the simpler solution!

[adam_k]

Well, I believed that using the length of the original string as the length parameter for the substring it would remove the last space (ie 10chars the original string + 2 chars for spaces =12 substring(newstring,2,10) would remove the last space.
I failed to take into account that by replacing North with N the original string would be 4 (+2 for the spaces) chars longer that the new one.
You were right once again. ltrim(rtrim( is the way to go.

Thanks for correcting me.