0

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'

3
Contributors
5
Replies
10
Views
5 Years
Discussion Span
Last Post by adam_k
0

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!

Edited by BitBlt: n/a

2

@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
Votes + Comments
Excellent solution!
0

@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!

Edited by BitBlt: n/a

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.